Jan
27
Written by:
Javier Callico
1/27/2009 12:00 AM
Today I needed to delete all foreign key contrains in a SQL 2005 database in order to be able to sync it using the Visual Studio Data Compare Tool. I found a script doing almost what I needed but after running it I noticed that it also dropped indexes and defaults in all the schemas. I made some changes to accomplish the following:
1- Drop foreign key contrains only.
2- Filter by schema.
3- Don't execute the drop statements automatically just print them.
Here is my script:
SET NOCOUNT ON
DECLARE @SchemaName VARCHAR(100)
SET @SchemaName = NULL -- for a specific schema name set it here
DECLARE @ObjectID INT
, @ObjectName VARCHAR(500)
, @SQL NVARCHAR(2000)
, @Count INT
, @ObjectType VARCHAR(1000)
CREATE TABLE #ObjectTemp (ObjectID INT IDENTITY(1,1) NOT NULL, ObjectName VARCHAR(250), ObjectType VARCHAR(100))
SET @Count = 0
INSERT INTO #ObjectTemp (ObjectName, ObjectType)
SELECT Table_Schema + '.[' + Table_Name + ']', Constraint_Name
FROM INFORMATION_SCHEMA.Table_CONSTRAINTS
WHERE (@SchemaName IS NULL OR UPPER(Table_Schema) = UPPER(@SchemaName))
AND Constraint_Type = 'FOREIGN KEY'
ORDER BY constraint_type , Table_Name
SELECT @ObjectID = MIN(ObjectID) FROM #ObjectTemp
WHILE @ObjectID IS NOT NULL
BEGIN
SELECT @ObjectName = ObjectName, @ObjectType = ObjectType
FROM #ObjectTemp WHERE ObjectID = @ObjectID
SET @SQL = 'ALTER TABLE ' + @ObjectName + ' DROP CONSTRAINT [' + @ObjectType + ']'
PRINT @SQL
-- uncomment this line to delete the contrains automatically
-- or copy and paste the output into a new query window and execute
--EXECUTE SP_EXECUTESQL @SQL
SELECT @ObjectID = MIN(ObjectID) FROM #ObjectTemp WHERE ObjectID > @ObjectID
SET @ObjectName = NULL
SET @SQL = NULL
SET @COUNT = @Count + 1
END
PRINT '--' + CAST(@Count AS VARCHAR(10)) + ' constraint(s) processed.'
DROP TABLE #ObjectTemp
Here is the original script:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22938194.html