How to set the primary key for each table

  • Hi all.

    I am a super noob with SQL server.  and I mean, brand spankin' new noob still with bright sparkly eyes, and dream of grandeur.

    So I've imported a pile of tables over from MS Access and discovered the Primary keys are not automatically set.  So now I need to loop through each table and set the primary key for each table
    typically this would be the ID field. I have found the string that allows for modifying at least one table, being:

    ALTER TABLE
    Tbl_MyTable
    ADD
    PRIMARY KEY (ID)

    But I have a ton of tables and need to be able to loop through them all.
    How do I do this?
    I expect something like:
    For each table in SQL database
       Alter Table
       Etc...
    next table

  • Will the Primary Key be the column ID on every table? Also, I assume, you'll want to follow up with Foreign Keys afterwards; not sure you're going to be able to automate that one.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • There's no 'for each table' construct

    You can write some complicated dynamic SQL to generate the ALTER  TABLE  statements automatically, check them all to make sure they're correct and then run them, or you can write the alter statements by hand.
    Unless you've got hundreds of tables, the time required will probably be much the same for the two options.

    And then you do still need to go and set the foreign keys, and those will have to be manual. Without foreign keys, SQL doesn't know how the tables are related.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It's technically unsupported, but you could use sp_msforeachtable to walk through and do what you want. Here's a link with examples.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I would usually use the System Information Schema views to generate a batch of SQL to run for cases like this. 

    https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/system-information-schema-views-transact-sql?view=sql-server-2017

    If all the primary key columns are ID it will be fairly easy to do.

    SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_NAME) + ' ADD PRIMARY KEY (ID)'

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE='BASE TABLE'

  • Thank you all for your replies!

    TripleAxe - this is exactly what I was looking for, thank you. I presume I can use this with the exec command and it should all just go.  

    Is it possible to add a component that checks if a PK is already in place, and then ignores it?

    So for example: (I constructed this from my very limited one day worth of experience in SQL coding, so it's not right).  Could I use this somehow like the following

    DECLARE @PKScript2 VARCHAR(max)='';

    SELECT @PKScript2 += 'SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_NAME) + ' ADD PRIMARY KEY (ID)'+
                        FROM INFORMATION_SCHEMA.TABLES+
                        WHERE TABLE_TYPE='BASE TABLE'
    FROM sys.identity_columns icol INNER JOIN
      sys.objects obj on icol.object_id= obj.object_id
    WHERE NOT EXISTS (SELECT * FROM sys.key_constraints k
          WHERE k.parent_object_id = obj.object_id
            AND k.type = 'PK')
      AND obj.type = 'U'
    --PRINT (@PKScript2);
    EXEC(@PKScript2);

    Suggestions welcomed.  thank you!

  • barry.nielson - Tuesday, July 31, 2018 4:56 PM

    Thank you all for your replies!

    TripleAxe - this is exactly what I was looking for, thank you. I presume I can use this with the exec command and it should all just go.  

    Is it possible to add a component that checks if a PK is already in place, and then ignores it?

    So for example: (I constructed this from my very limited one day worth of experience in SQL coding, so it's not right).  Could I use this somehow like the following

    DECLARE @PKScript2 VARCHAR(max)='';

    SELECT @PKScript2 += 'SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_NAME) + ' ADD PRIMARY KEY (ID)'+
                        FROM INFORMATION_SCHEMA.TABLES+
                        WHERE TABLE_TYPE='BASE TABLE'
    FROM sys.identity_columns icol INNER JOIN
      sys.objects obj on icol.object_id= obj.object_id
    WHERE NOT EXISTS (SELECT * FROM sys.key_constraints k
          WHERE k.parent_object_id = obj.object_id
            AND k.type = 'PK')
      AND obj.type = 'U'
    --PRINT (@PKScript2);
    EXEC(@PKScript2);

    Suggestions welcomed.  thank you!

    DECLARE @PKScript2 VARCHAR(MAX)='';
    DECLARE @Newline NVARCHAR(MAX)=';' + CHAR(13)
    SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' ADD PRIMARY KEY ('+ QUOTENAME(icol.name) + ')' + @Newline
    FROM INFORMATION_SCHEMA.TABLES T
    INNER JOIN sys.identity_columns icol
       on object_name(icol.object_id)=T.TABLE_NAME
            AND object_schema_name(icol.object_id)=T.TABLE_SCHEMA
    INNER JOIN sys.objects obj on icol.object_id= obj.object_id
    WHERE TABLE_TYPE='BASE TABLE'
     AND NOT EXISTS (SELECT *
           FROM sys.key_constraints k
           WHERE k.parent_object_id = obj.object_id
            AND k.type = 'PK')
     AND obj.type = 'U'
     ORDER BY TABLE_NAME
    PRINT (@PKScript2);
    --EXEC(@PKScript2);

  • Thank you Johnathan.

    This seems awesome!

    The problem is I ended up having to manually create the PK on all tables, which took ages.  so to run this, I removed the PK from one of the tables and then ran this code you provided. 
    It did not seem to display the PK for that table via the print.  So I cannot tell if it works as intended.  Am I doing something wrong?

    I should also say, PKScript variable does not appear to be populated, so I can't tell how this is going to generate a result.  Is this intentional?

  • barry.nielson - Tuesday, July 31, 2018 5:28 PM

    Thank you Johnathan.

    This seems awesome!

    The problem is I ended up having to manually create the PK on all tables, which took ages.  so to run this, I removed the PK from one of the tables and then ran this code you provided. 
    It did not seem to display the PK for that table via the print.  So I cannot tell if it works as intended.  Am I doing something wrong?

    I should also say, PKScript variable does not appear to be populated, so I can't tell how this is going to generate a result.  Is this intentional?

    I left that off so the results come out in the grid which you can manually just copy and paste into a script (it gives you the opportunity to check it). If you want the old functionality back here it is:
    DECLARE @PKScript2 NVARCHAR(MAX)=''
    DECLARE @Newline NVARCHAR(MAX)=';' + CHAR(13)
    SELECT @PKScript2 += 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' ADD PRIMARY KEY ('+ QUOTENAME(icol.name) + ')' + @Newline
    FROM INFORMATION_SCHEMA.TABLES T
    INNER JOIN sys.identity_columns icol
    on object_name(icol.object_id)=T.TABLE_NAME
     AND object_schema_name(icol.object_id)=T.TABLE_SCHEMA
    INNER JOIN sys.objects obj on icol.object_id= obj.object_id
    WHERE TABLE_TYPE='BASE TABLE'
    AND NOT EXISTS (SELECT *
     FROM sys.key_constraints k
     WHERE k.parent_object_id = obj.object_id
     AND k.type = 'PK')
    AND obj.type = 'U'
    ORDER BY TABLE_NAME
    PRINT (@PKScript2);
    --EXEC(@PKScript2);

  • Thanks again Johnathan for your quick response.
    Unfortunately this is not generating a value in my messages window and not applying a PK to any table, even if I switch on the exec command. 
    I have removed the PK from 3 tables to see this working, but it seems not to, and sadly I do not have the know how to figure out why.  
    Thanks for your assistance.

  • It's producing results on my database.

  • barry.nielson - Tuesday, July 31, 2018 6:26 PM

    Thanks again Johnathan for your quick response.
    Unfortunately this is not generating a value in my messages window and not applying a PK to any table, even if I switch on the exec command. 
    I have removed the PK from 3 tables to see this working, but it seems not to, and sadly I do not have the know how to figure out why.  
    Thanks for your assistance.

    Try writing a script using cursor

  • Make sure you're in the right database first, by putting:

    Use MyDatabaseName
    Go

    At the top of the file (replacing MyDatabaseName with the name of your database)

  • barry.nielson - Monday, July 30, 2018 10:29 PM

    Hi all.

    I am a super noob with SQL server.  and I mean, brand spankin' new noob still with bright sparkly eyes, and dream of grandeur.

    So I've imported a pile of tables over from MS Access and discovered the Primary keys are not automatically set.  So now I need to loop through each table and set the primary key for each table
    typically this would be the ID field. I have found the string that allows for modifying at least one table, being:

    ALTER TABLE
    Tbl_MyTable
    ADD
    PRIMARY KEY (ID)

    But I have a ton of tables and need to be able to loop through them all.
    How do I do this?
    I expect something like:
    For each table in SQL database
       Alter Table
       Etc...
    next table

    I'm going to tell you how to cheat. 🙂

    Mind you, you'll need to buy the tool I'm recommending but for a newbie (assuming you have MS Access experience at least) this will let you not only solve your issue but two or three others you don't know you're about to run into.

    The tool is called ModelRight and while it's not cheap ($595 for the SQL Server only version) it will let you AUTOMATICALLY suck up your MS Access schema into a "database CAD" (actually called an ERD tool), preserving all primary and foreign keys and then generate the SQL Server script that you can then execute to create your SQL database--complete with primary keys, foreign keys, indexes and constraints you created on the Access side.

    Problem solved. 🙂

    In addition it will let you document your SQL Server schema, including notes that will be made into extended properties that SSMS can read--and that's a painless instant way to document your tables and columns! This is a GODSEND, trust me. Further, it will give you a tool to make future modifications to your SQL database--then create the scripts to add columns, tables, etc. All while letting you document it with pretty pictures and text comments accessible via SSMS.

    It isn't free, but I swear to you it's like getting an assistant DBA of your very own.

    Also, speaking of Godsend tools if I were you I'd SERIOUSLY bug your boss to buy Redgate's SQL Compare and SQL Data Compare. These let you update the schema of one database (say, QA or production) with the schema of another (say Development). Painlessly. Without forgetting anything.

    Data compare lets you transfer data from one database to another, it's great for updating the dev database from either the QA or production databases. Again, not exactly cheap but they will pay for themselves really quickly.

    Good luck!

  • coolchaitu - Thursday, August 2, 2018 6:02 AM

    Try writing a script using cursor

    Why?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 20 total)

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