April 21, 2006 at 1:45 am
Hi,
In our product database we created a Primary Key Constraint without a specific name. The PK Constraint name may be different in different database servers. For e.g, PK__TestTable__753864A1 .
Now we want to drop this table's constraint using a runtime SQL script during the product install. Something like as follows:
DECLARE @val VARCHAR(50)
select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'
ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT @val
The constraint's name will be a variable and how can we drop it? The above query throws an error as you know.
Thanks in advance.
Regards
Unnic
April 21, 2006 at 1:51 am
You'll need to use dynamic SQL in this case, as an alter table can't take a variable.
DECLARE @val VARCHAR(50)
select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'
EXECUTE ('ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT ' + @val)
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
April 21, 2006 at 2:42 am
Thanks Gila..
But the above query throws an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'CONSTRAINT'.
Any idea how can I solve it?
Regards
Unnic
April 21, 2006 at 3:28 am
If you print the string instead of executing it, what does it contain?
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
April 21, 2006 at 3:43 am
April 21, 2006 at 3:56 am
What does this return?
DECLARE @val VARCHAR(50)
SELECT name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'
select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'
PRINT ' @val has the value ' + ISNULL(@Val,'')
PRINT 'ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT [' + ISNULL(@Val,'') + ']'
Is there a pk with the name starting PK__TestTable? A better way to get the pk name of the table 'VOIP_CUEUsers' something like this
SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsPrimaryKey') = 1 AND OBJECT_NAME(parent_obj) = 'VOIP_CUEUsers'
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
April 21, 2006 at 4:03 am
Thank you very much Gila..
It was my mistake that I used PK_TestTable, but the table, TestTable and the constraint was not existing. When I used an existing table it worked !
Thanks
Regards
Unnic
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy