June 4, 2008 at 9:19 am
Hello all.
OK i have script which is to be run on several databases. Within this script there are commands to create a primary key on a specific table. Can anyone tell me if it is possible to check if a specific primary key exists on a table?
Thanks people.
June 4, 2008 at 9:24 am
IF EXITS (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_NAME = '..... your table name ....'
AND TABLE_SCHEMA ='dbo' )
BEGIN
---- Your code here
END
* Noel
June 4, 2008 at 9:32 am
Hello there. Thanks very much for taking the time to reply.
OK..........ive taken your code and modified it as such:
IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_NAME = '[tblReturnVoucher]'
AND TABLE_SCHEMA ='dbo' )
BEGIN
ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED ([ID])
GO
PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
END
Hoever this gives me the following errors:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'END'.
Thanks again for your help.
June 4, 2008 at 9:37 am
gavin.duncan (6/4/2008)
Hello there. Thanks very much for taking the time to reply.OK..........ive taken your code and modified it as such:
IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'AND TABLE_NAME = '[tblReturnVoucher]'
AND TABLE_SCHEMA ='dbo' )
BEGIN
ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED ([ID])
GO
PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
END
Hoever this gives me the following errors:
Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'END'.
Thanks again for your help.
You must remove the "GO"s
* Noel
June 4, 2008 at 9:40 am
Hi. Once again thanks for your swift response 🙂
OK..........i removed the 'GO's and the code i am left with is:
IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_NAME = '[tblReturnVoucher]'
AND TABLE_SCHEMA ='dbo' )
BEGIN
ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED [ID])
PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
END
However this still leaves me with the following:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Many thanks again.
June 4, 2008 at 9:50 am
gavin.duncan (6/4/2008)
Hi. Once again thanks for your swift response 🙂OK..........i removed the 'GO's and the code i am left with is:
IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'AND TABLE_NAME = '[tblReturnVoucher]'
AND TABLE_SCHEMA ='dbo' )
BEGIN
ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED [ID])
PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
END
However this still leaves me with the following:
Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Many thanks again.
Your last Line has two "BEGIN" which is confusing the parser!!
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
should probably be:
IF @@TRANCOUNT=0
BEGIN
INSERT INTO #tmpErrors (Error) SELECT 1
COMMIT TRANSACTION ---<<< I am NOT sure why are you doing this though
END
* Noel
June 4, 2008 at 9:54 am
Hello noel..........again many thanks. However it would appear the error is definately in the first few lines. I stripped it back to the following:
IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_NAME = '[tblReturnVoucher]'
AND TABLE_SCHEMA ='dbo' )
BEGIN
PRINT N'TABLE HAS NO PRIMARY KEY'
END
And im still getting the following error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Thanks again.
June 4, 2008 at 9:58 am
hehe :
IF NOT EXITS
should be:
IF NOT EXISTS
* Noel
June 4, 2008 at 10:01 am
Aaaaaaarrrrrgghh!!! :blush:
Thank you very much noel. Much appreciated!
June 4, 2008 at 10:06 am
No problem ... it was MY typo 😀
* Noel
June 4, 2008 at 10:07 am
Noooooo the saga continues!
OK........ive made the changes and have ended up with the following:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_NAME = '[tblReturnVoucher]'
AND TABLE_SCHEMA ='dbo' )
BEGIN
ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED ([ID])
PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'
END
Now by my understanding (which may well be wrong) the above should only create the primary key if it does not currently have one. However when i run the above for a second time i get the following errors indicating that the primary key already exists (which is correct) but it still tries to create it again :crying: :
Msg 1779, Level 16, State 0, Line 5
Table 'tblReturnVoucher' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 5
Could not create constraint. See previous errors.
Thanks again dude.
June 4, 2008 at 11:14 am
gavin.duncan (6/4/2008)
Noooooo the saga continues!OK........ive made the changes and have ended up with the following:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'AND TABLE_NAME = '[tblReturnVoucher]'
AND TABLE_SCHEMA ='dbo' )
BEGIN
ALTER TABLE [dbo].[tblReturnVoucher] ADD CONSTRAINT [PK_ReturnVoucher] PRIMARY KEY CLUSTERED ([ID])
PRINT N'Creating primary key [PK_ReturnVoucher] on [dbo].[tblReturnVoucher]'
END
Now by my understanding (which may well be wrong) the above should only create the primary key if it does not currently have one. However when i run the above for a second time i get the following errors indicating that the primary key already exists (which is correct) but it still tries to create it again :crying: :
Msg 1779, Level 16, State 0, Line 5Table 'tblReturnVoucher' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 5
Could not create constraint. See previous errors.
Thanks again dude.
Remove the square brakets on the exists .... like:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_NAME = 'tblReturnVoucher'
AND TABLE_SCHEMA ='dbo' )
....
* Noel
June 5, 2008 at 2:05 am
Brilliant.
Thanks again Noel..........much appreciated.
October 13, 2010 at 11:10 pm
Same thing but this checks all tables for missing PKs:
SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE TABLE_NAME NOT IN
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C WHERE CONSTRAINT_TYPE = 'PRIMARY KEY')
dbgurus.com.au
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply