3

I want to add a new column to a table. The table is already populated with data.

I can do this by making a complete copy of a table, inlcuding the contents, and add a Primary Key ID column to it.

I can create a new table thus:

create table myTestTable
(
    ID uniqueidentifier NOT NULL,
    SomeId uniqueidentifier NOT NULL,
    SomeOtherId uniqueidentifier NOT NULL,
    constraint table_constraint PRIMARY KEY (ID)
)

-- modify the script below to copy the old table across to the new one,
-- generating a unique ID (GUID) into

insert into myTestTable
(ID,
ClaimPaymentId,
TemplateId)
select
    NEWID(),
    SomeId ,
    SomeOtherId 
from [dbo].[TableToBeCopied]

I can then drop the copied table and rename the copy to the old table name.

But I have to do about sixty such tables and I want to write a script which will take a table's name as a parameter and automatically generate the column definitions. But I can't see how to do this. Any ideas?

2

You can add your GUID ID column with the following.

ALTER TABLE [yourSchema].[yourTable] add [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL

To do this for multiple tables you could use something like this:

SELECT 'ALTER TABLE '+QUOTENAME(s.name)+'.'+QUOTENAME(t.name)+' add [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL'

FROM sys.schemas s
JOIN sys.tables t ON t.schema_id = s.schema_id

WHERE (     (s.name = 'Schema1' AND t.name IN ('Schema1_TableList'))
            OR
            (s.name = 'Schema2' AND t.name IN ('Schema2_TableList'))
            ... -- Add more schemas and their tables as needed
        )

This will give you all the ALTER TABLE statements to create an ID UNIQUEIDENTIFER coulmn with the ID values pre populated.

0

I supposed the Primary Key always contains 1 column but you can easily add 1 or more columns pkcol1, pkcol2, ... to the list.

Note that it is usually a bad idea to use a GUID as a Primary Key or Clustered Index because it does not create consecutive numbers (with NEWID())and it is not a narrow value. NEWSEQUENTIALID() can be used to create sequential GUIDs although it is still a wide PK (16 bytes).

Create Statements

SELECT 'ALTER TABLE '+QUOTENAME(schemaname)+'.'+QUOTENAME(tablename)+' ADD '+QUOTENAME(pkcol)+' UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_'+tablename+' DEFAULT newid();
ALTER TABLE '+QUOTENAME(schemaname)+'.'+QUOTENAME(tablename)+' ADD CONSTRAINT PK_'+tablename+'_'+pkcol+' PRIMARY KEY CLUSTERED ('+QUOTENAME(pkcol)+');
'
FROM (values('schema', 'table1', 'id')
            , ('schema', 'table2', 'id')
            , ('schema', 'table3', 'id')
            , ('...', '...', '...')
            , ...
) as tableList(schemaname, tablename, pkcol);
  • The output gives you a list of all the ALTER statements for all the tables in the list and they can then be checked out, copied and executed together or one by one

    ALTER TABLE [schema].[table1] ADD [id] UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_table1 DEFAULT newid();
    ALTER TABLE [schema].[table1] ADD CONSTRAINT PK_table1_id PRIMARY KEY CLUSTERED ([id]);
    ...
    
  • Adapt the ALTER statements in the SELECT to your needs (CLUSTERED or NONCLUSTERED, table name, constaint name, ...)

  • More table schemas, names and PK cols can be added to the list
  • You can avoid errors by making sure the table names exist with a JOIN on name between tableList and sys.tables

Print or Execute

Using the same query with a @sql variable:

DECLARE @sql nvarchar(max) = ''

SELECT @sql = @sql + 'ALTER TABLE '+ QUOTENAME(schemaname) +'.'+QUOTENAME(tablename)+' 
    ADD CONSTRAINT PK_'+ tablename +'_'+ pkcol +' PRIMARY KEY CLUSTERED ('+ QUOTENAME(pk) +');
'
FROM ... ;

You can then print the first 4000 characters with:

Print @sql;

ALTER TABLE [schema].[table1] ADD [id] UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_table1 DEFAULT newid();
ALTER TABLE [schema].[table1] ADD CONSTRAINT PK_table1_id PRIMARY KEY CLUSTERED ([id]);
...

Or execute all the ALTER statements with:

EXEC sp_executesql @sql;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.