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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply