Professional Web Applications Themes

looping through user tables to add a field ? - Microsoft SQL / MS SQL Server

James, Two options.. One would be to use the undoented call sp_msforeachtable, as in sp_msforeachtable 'ALTER TABLE ? ADD NEWCOL INT NULL' Since its a undoented call, please refrain from using it in production code. Another option would be to generate sql scripts, as in SELECT 'ALTER TABLE '+QUOTENAME(table_name)+' ADD NEWCOL INT NULL'+char(13)+'GO' FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND table_schema='dbo' -- Dinesh. SQL Server FAQ at http://www.tkdinesh.com "james" <net> wrote in message news:%23%phx.gbl... ...

  1. #1

    Default Re: looping through user tables to add a field ?

    James,

    Two options..
    One would be to use the undoented call sp_msforeachtable, as in

    sp_msforeachtable 'ALTER TABLE ? ADD NEWCOL INT NULL'

    Since its a undoented call, please refrain from using it in production
    code.

    Another option would be to generate sql scripts, as in

    SELECT 'ALTER TABLE '+QUOTENAME(table_name)+' ADD NEWCOL INT
    NULL'+char(13)+'GO'
    FROM INFORMATION_SCHEMA.TABLES
    WHERE table_type = 'BASE TABLE'
    AND table_schema='dbo'


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "james" <net> wrote in message
    news:%23%phx.gbl... 


    Dinesh.T.K Guest

  2. #2

    Default Re: looping through user tables to add a field ?

    ...the second option would generate a series of ALTER TABLE... commands for
    all the tables for that owner.You can then copy this code and execute it .

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Dinesh.T.K Guest

  3. #3

    Default Re: looping through user tables to add a field ?

    James,

    In SQL Server you can use cursors.Infact if you check the source code of the
    undoented call, its using cursors.I would be more than happy if they make
    sp_msforeach* series as doented :). To check the source code, do

    USE master
    sp_helptext sp_msforeachtable


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "james" <net> wrote in message
    news:phx.gbl... [/ref]
    for [/ref]
    .. [/ref]
    > production 
    > >
    > >[/ref]
    >
    >[/ref]


    Dinesh.T.K Guest

  4. #4

    Default Re: looping through user tables to add a field ?

    Dinesh,

    thanks for the code. Sure would be nice if MS would add
    language like this

    foreach ( Table t in UserTables )
    Table.Alter ...

    too much C# I guess :)

    JIM


    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:ugJP4$phx.gbl... [/ref]
    production 
    > >
    > >[/ref]
    >
    >[/ref]


    james Guest

  5. #5

    Default Re: looping through user tables to add a field ?

    Neat. thanks

    JIM


    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:phx.gbl... 
    the 
    make [/ref]
    > for [/ref][/ref]
    it 
    > > production 
    > >
    > >[/ref]
    >
    >[/ref]


    james Guest

Similar Threads

  1. Replies: 4
    Last Post: October 11th, 11:30 PM
  2. Select, Join & field values - 2 tables
    By Justin Koivisto in forum MySQL
    Replies: 3
    Last Post: February 13th, 08:06 PM
  3. Displaying scrollable tables in text field...
    By wbahan in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: August 27th, 05:47 PM
  4. Looping through a field and deleting first char
    By Brett in forum Macromedia Director Lingo
    Replies: 5
    Last Post: January 7th, 06:04 PM
  5. Replies: 1
    Last Post: November 29th, 05:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139