Deleting foreign keys - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Deleting foreign keys

Author

Topic

iamlerxt
Starting Member

11 Posts

Posted - 2005-08-01 : 17:56:03

Could somebody please tell me why this snippet of tsql code will not work for me?? I am trying to delete every foreign key contraint from every user table in my database. If I comment out the "ALTER TABLE" statement, it lists my constraints and table names perfectly, so I know that part is working. The problem is a "syntax error near @Table", so obviously it does not like me using a varchar variable in that statement, but I don't know the proper way to do this.
Thanks for your time.

USE POS2000
GO

DECLARE @FK varchar(100), @Table varchar(100)
--Select foreign key constraints for user tables only
DECLARE fk_cursor CURSOR FOR
select so.name as constraintname,so1.name as tablename
from sysconstraints as sc
inner join sysobjects as so on sc.Constid = so.id
inner join sysobjects as so1 on sc.id = so1.id
where so.xtype ='f' and so1.xtype='u'

OPEN fk_cursor

FETCH NEXT FROM fk_cursor
INTO @FK, @Table

WHILE @@FETCH_STATUS = 0
BEGIN

-- Display current values and execute query to drop the constraint
PRINT 'Dropping foreign key constraint -- Table: ' + @Table + ' Key:' + @FK

ALTER TABLE @Table DROP CONSTRAINT @FK

-- Get the next table
FETCH NEXT FROM fk_cursor
INTO @FK, @Table
END

--Destroy the cursor
CLOSE fk_cursor
DEALLOCATE fk_cursor
GO

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-01 : 17:58:00

You will need to use dynamic sql in this case to concatenate @tablename into the command, then execute the command. Review sp_executesql in BOL.

-- example
declare @cmd varchar(1000)
set @cmd = 'alter table ' + @table_name + ' drop ' + @fk_name
exec sp_executesql @cmd

Nathan Skerl

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-01 : 18:00:36

>> ALTER TABLE @Table DROP CONSTRAINT @FK
You can't use variable object names in SQL.

The only way to solve that is to use "dynamic sql".
Refer http://www.sqlteam.com/item.asp?ItemID=4599

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-01 : 18:01:27

rockmoose

iamlerxt
Starting Member

11 Posts

Posted - 2005-08-01 : 18:04:46

Thanks to moose and nathans. Unbelievable response time. What was it, like, 2 minutes??? Your solution worked wonderfully and I learned something new that will be very valuable to me from now on.
Thanks a ton to both of you.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-01 : 18:41:07

Thx for the Thx

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources