Easy Drop and ReBuild Constraints with an SP

				
--**************************************
-- Name: Easy Drop and ReBuild Constraints with an SP
-- Description:This code is generating an SQL script, that drop or build referentials.
-- By: Zoltan Tamas, Toth
--
-- Inputs:Table name and a flag.
The flag determines, that the constraint should drop or rebuild.
Example:
Generate DROP constraints script:
EXEC sp_Drop_Or_Rebuild_Refs
 @i_Table_Name = 'Employee',
 @i_Is_Drop = 1
Generate Build constraints script:
EXEC sp_Drop_Or_Rebuild_Refs
 @i_Table_Name = 'Employee',
 @i_Is_Drop = 0
--
-- Returns:SP returns the SQL script like:
"ALTER TABLE table_name DROP CONSTRAINT constraint_name
go"....etc..
If you want to generate an "Rebuild" script from your referentials, you must generate the script before you drop the constraints.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=440&lngWId=5--for details.--**************************************

--------------------
-- //////////////////
-- // w_Contraints //
-- //////////////////
IF EXISTS( SELECT Id FROM SysObjects WHERE Name = N'w_Contraints' AND type = 'V' ) 
 DROP VIEW w_Contraints
GO
CREATE VIEW w_Contraints
AS
SELECT 
 o1.name AS Constraint_Name,
 om.name AS Master_Table_Name,
 cm.Name AS Master_Field_Name,
 od.name AS Detail_Table_Name,
 cd.Name AS Detail_Field_Name
FROM 
 sysobjects AS o
 JOIN sysreferences AS sr ON( 
( sr.rkeyid = o.ID ) -- OR ( sr.fkeyid = o.ID ) -- ha a felfelé induló relációk is érdekelnek
 )
 JOIN sysobjects AS o1 ON( o1.ID = sr.constid )
 JOIN sysobjects AS od ON( od.ID = sr.fkeyid )
 JOIN sysobjects AS om ON( om.ID = sr.rkeyid )
 JOIN syscolumns AS cm ON( cm.ID = om.ID AND cm.colid = sr.rkey1 )
 JOIN syscolumns AS cd ON( cd.ID = od.ID AND cd.colid = sr.fkey1 )
GO
GRANT SELECT ON w_Contraints TO PUBLIC
GO
--------------------------------------------------
-- /////////////////////////////
-- // sp_Drop_Or_Rebuild_Refs //
-- /////////////////////////////
IF EXISTS( SELECT Id FROM SysObjects WHERE Name = N'sp_Drop_Or_Rebuild_Refs' AND type = 'P' ) 
 DROP PROCEDURE sp_Drop_Or_Rebuild_Refs
GO
CREATE PROCEDURE sp_Drop_Or_Rebuild_Refs
 @i_Table_Name nvarchar(255),
 @i_Is_Drop int
AS
 SET NOCOUNT ON
/*SELECT 
 o1.name AS Constraint_Name,
 om.name AS Master_Table_Name,
 cm.Name AS Master_Field_Name,
 od.name AS Detail_Table_Name,
 cd.Name AS Detail_Field_Name
FROM 
 sysobjects AS o
 JOIN sysreferences AS sr ON( 
( sr.rkeyid = o.ID ) -- OR ( sr.fkeyid = o.ID ) -- ha a felfelé induló relációk is érdekelnek
 )
 JOIN sysobjects AS o1 ON( o1.ID = sr.constid )
 JOIN sysobjects AS od ON( od.ID = sr.fkeyid )
 JOIN sysobjects AS om ON( om.ID = sr.rkeyid )
 JOIN syscolumns AS cm ON( cm.ID = om.ID AND cm.colid = sr.rkey1 )
 JOIN syscolumns AS cd ON( cd.ID = od.ID AND cd.colid = sr.fkey1 )
WHERE 
 o.Name = 'categories'
*/
if @i_Is_Drop = 1 begin
 SELECT 
'ALTER TABLE ' + Detail_Table_Name + ' DROP CONSTRAINT ' + Constraint_Name + '
go'
 FROM 
w_Contraints
 WHERE 
Master_Table_Name = @i_Table_Name
end else begin
 SELECT 
'ALTER TABLE ' + Detail_Table_Name + '
 ADD CONSTRAINT ' + Constraint_Name + ' FOREIGN KEY (
' + Master_Field_Name + '
 ) REFERENCES ' + Master_Table_Name + ' (
' + Detail_Field_Name + '
 )
go'
 FROM 
w_Contraints
 WHERE 
Master_Table_Name = @i_Table_Name
end
 RETURN( 0 )
GO
GRANT EXECUTE ON sp_Drop_Or_Rebuild_Refs TO Public
GO
---------
--Example:
--EXEC sp_Drop_Or_Rebuild_Refs
-- @i_Table_Name = 'Employee',
-- @i_Is_Drop = 0