There is a DB, in it some tables. Each table has an id column. The question is how to make a single column id column primary key? Those. so that each table has a primary key (id).

    1 answer 1

    In the system catalogs, we find tables that have an ID column, but no primary key, and generate an SQL request for them to add it:

     select N'alter table ' + quotename(s.name) + N'.' + quotename(t.name) + N' add constraint ' + quotename(N'PK_' + t.name) + N' primary key ([ID]) GO' from sys.schemas s join sys.tables t on t.schema_id = s.schema_id join sys.columns c on c.object_id = t.object_id and c.name = N'ID' where not exists ( select 1 from sys.key_constraints k where k.parent_object_id = t.object_id and k.type = 'PK' )

    We execute this request in SSMS, as a result of which SQL code is generated like:

     alter table [dbo].[TableName] add constraint [PK_TableName] primary key (ID) GO alter table [dbo].[TableName2] add constraint [PK_TableName2] primary key (ID) GO ...

    This code is copied to a new window and executed.

    If automatic execution is needed, instead of manual copying and execution, we wrap this query in the cursor and execute it using exec(@sql) (you only need to remove the GO separator from concatenation).

    • how will automatic execution, alter table [ ] what I need to write - propro17
    • I have a lot of tables - propro17
    • Неправильный синтаксис около конструкции "+" line 2 - propro17
    • @ propro17, "incorrect syntax" - are you GO cleaning? Maybe the quotes are erased. I copied the script from the answer, drove it through the test database - everything works. Do you really need automatic execution? If this is a one-time script, then the easiest thing to do is as written in the answer. In SqlServer Management Studio run the query, it will generate commands. Copy them all at once into the buffer, open a new query window (press Ctrl + N), paste the commands from the buffer and launch it for execution. - i-one