February 17, 2004 at 11:07 am
I have: SET @oldtable = (select a.name from sysobjects b inner join sysobjects a on (a.id = b.parent_obj and b.name = 'PK_biomart' and b.xtype = 'PK')) How do I get this statement to use my variable: ALTER TABLE @oldtable drop CONSTRAINT [PK_biomart]
February 17, 2004 at 11:53 am
This might work:
set @oldpk = (select name from sysindexes i where id =
object_id(@oldtable) and indid = 1)
indid = 1 -- Indicates the clustered index which frequently is the key.
Also try:
set @oldpkk = (select Constraint_Name from information_schema.Table_Constraints
where Table_Name = @oldtable and Constraint_Type = 'Primary Key')
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
February 23, 2004 at 5:54 am
SET @oldtable = (select a.name from sysobjects b inner join sysobjects a on (a.id = b.parent_obj and b.name = 'PK_biomart' and b.xtype = 'PK'))
declare @stmt nvarchar(100)
set @stmt = 'ALTER TABLE '+@oldtable+ ' drop CONSTRAINT [PK_biomart]'
exec sp_executesql @stmt
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 23, 2004 at 7:16 am
Thanks for the assist Frank. This also seems to work ok: DECLARE @oldtable sysname SET @oldtable = (select a.name from sysobjects b inner join sysobjects a on (a.id = b.parent_obj and b.name = 'PK_biomart' and b.xtype = 'PK')) EXEC ('ALTER TABLE ' + @oldtable + ' drop CONSTRAINT [PK_biomart]')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply