Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Add similar fields to many tables

    Folks, i have to create four fields in every user table within my database:
    CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]
    There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!
    declare @name VARCHAR (50)
    declare cur cursor
    fast_forward
    for select name from sysobjects where type='u' and status not like '-%'
    open cur
    WHILE (1=1)
    BEGIN
    FETCH NEXT
    FROM cur
    INTO @name
    IF @@fetch_status = 0
    BEGIN
    ALTER TABLE @name
    ADD created_by [VARCHAR] (25)
    GO
    ALTER TABLE @name
    ADD created_by [VARCHAR] (25)
    GO
    ALTER TABLE @name
    ADD created_date [DATETIME]
    GO
    ALTER TABLE @name
    ADD modified_by [VARCHAR] (25)
    GO
    ALTER TABLE @name
    ADD modified_date [DATETIME]
    END
    ELSE
    BREAK
    END
    DEALLOCATE cur
    I also want that if one column for a table exists; the other columns should be created rather than it quits.


    Howdy!

  2. #2
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Folks, please help!
    Or do i have to add the columns manually!


    Howdy!

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Maybe if you are concerned about whether a column already exists it would be easier to run the four different ALTER statements separately rather than coding around possibilities...y'know, four scripts that you run against all the tables instead of one.

    Just a suggestion.

    But for a one-time modification like this, you have to be careful that you don't spend more time trying to devise the most optimal and elegant solution than it would take you to just knuckle down and do the dirty work.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63

    auto add

    declare @tablename varchar(50)
    declare @add_field varchar(255)

    declare cur_tables cursor for
    select name from sysobjects where type='u' and status not like '-%'

    open cur_tables
    fetch next from cur_tables into @tablename
    while @@fetch_status = 0
    begin
    --print 'table name is ' + @tablename

    set @add_field = 'alter table ' + @tablename + ' add created_by varchar(25)'
    --print '@add_field is ' + @add_field
    exec (@add_field)

    set @add_field = 'alter table ' + @tablename + ' add created_date smalldatetime'
    --print '@add_field is ' + @add_field
    exec (@add_field)

    set @add_field = 'alter table ' + @tablename + ' add modified_by varchar(25)'
    --print '@add_field is ' + @add_field
    exec (@add_field)

    set @add_field = 'alter table ' + @tablename + ' add modified_date smalldatetime'
    --print '@add_field is ' + @add_field
    exec (@add_field)

    fetch next from cur_tables into @tablename
    end
    close cur_tables
    deallocate cur_tables

  5. #5
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    -- Run this and cut results to query window & run it.
    -- Tim S


    SET NOCOUNT ON;
    SELECT
    CASE WHEN col1.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_BY VARCHAR(25);' + CHAR(10) ELSE '' END +
    CASE WHEN col2.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_DATE [DATETIME];'+ CHAR(10) ELSE '' END +
    CASE WHEN col3.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_BY VARCHAR(25);'+ CHAR(10) ELSE '' END +
    CASE WHEN col4.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_DATE [DATETIME];'+ CHAR(10) ELSE '' END
    FROM INFORMATION_SCHEMA.TABLES tab
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS col1
    ON tab.TABLE_NAME = col1.TABLE_NAME AND tab.TABLE_SCHEMA = col1.TABLE_SCHEMA AND 'CREATED_BY' = col1.COLUMN_NAME
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS col2
    ON tab.TABLE_NAME = col2.TABLE_NAME AND tab.TABLE_SCHEMA = col2.TABLE_SCHEMA AND 'CREATED_DATE' = col2.COLUMN_NAME
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS col3
    ON tab.TABLE_NAME = col3.TABLE_NAME AND tab.TABLE_SCHEMA = col3.TABLE_SCHEMA AND 'MODIFIED_BY' = col3.COLUMN_NAME
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS col4
    ON tab.TABLE_NAME = col4.TABLE_NAME AND tab.TABLE_SCHEMA = col4.TABLE_SCHEMA AND 'MODIFIED_DATE' = col4.COLUMN_NAME
    WHERE tab.TABLE_TYPE = 'BASE TABLE' AND -- tab.TABLE_NAME = 'test2' AND
    ( col1.COLUMN_NAME IS NULL OR col2.COLUMN_NAME IS NULL OR col3.COLUMN_NAME IS NULL OR col4.COLUMN_NAME IS NULL)
    Last edited by TimS; 12-15-04 at 18:16.

Posting Permissions

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