Post reply

Add a column to all tables in a database

  • November 3, 2003 at 9:45 am

    #82533

    I want to Add a uniqueidentifier column to all tables in a database with rowguid property. It will be not null and be a primary key also. I can do it for one table at a time but not all tables in the database. Can someone give me the script to add this coloumn to all tables.

    Thank you very much

  • November 3, 2003 at 7:19 pm

    #480722

    Yikes,

    First you will need to create a script to script out the tables. Then you will need to rename all the tables to a new name, modify your script to add in the rowguid field and change the PK of your table. Then run the script, copy the data from your old tables to the newly created tables and finally drop the old tables. Then TEST, TEST, TEST to make sure everything went right.

    Can I ask why you need to do this?

    BTW: You can use something like the following to simply add the column if you are running SQL2K....

    SELECT 'ALTER TABLE ' + TABLE_NAME + ' ADD rowguidfield uniqueidentifier DEFAULT (newid())'

    FROM INFORMATION_SCHEMA.TABLES

    That will create a script you can then copy into a new window to add the field...

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply