July 30, 2018 at 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
July 31, 2018 at 12:12 am
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
July 31, 2018 at 5:33 am
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
July 31, 2018 at 6:17 am
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
July 31, 2018 at 7:26 am
I would usually use the System Information Schema views to generate a batch of SQL to run for cases like this.
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'
July 31, 2018 at 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!
July 31, 2018 at 5:18 pm
barry.nielson - Tuesday, July 31, 2018 4:56 PMThank 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);
July 31, 2018 at 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?
July 31, 2018 at 5:39 pm
barry.nielson - Tuesday, July 31, 2018 5:28 PMThank 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);
July 31, 2018 at 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.
July 31, 2018 at 6:40 pm
It's producing results on my database.
August 2, 2018 at 6:02 am
barry.nielson - Tuesday, July 31, 2018 6:26 PMThanks 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
August 2, 2018 at 11:56 am
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)
August 3, 2018 at 8:44 am
barry.nielson - Monday, July 30, 2018 10:29 PMHi 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!
August 3, 2018 at 8:49 am
coolchaitu - Thursday, August 2, 2018 6:02 AMTry writing a script using cursor
Why?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply