Fast way to deal with foreign keys

First of all, this is my first post and my English is realy poor so please do not judge me too hard... In this post I want to help people who are doing a lot of copy and paste when creating foreign key constraint and really hate to do this all the time. I have beed creating tables for more than 8 years and each time I create a foreign key there are 2 things that I have to do manually: First, create a drop script for the foreign key, when recreating the referenced table: Something like IF OBJECT_ID('foreignkeyname') IS NOT NULL ALTER TABLE reffering_table DROP CONSTRAINT foreignkeynameGO Well this is not big deal but when there are lots of relationships between tables this is time consuming and it is about a lot copy and paste after all.. Second, create an index for the reffering field. Almost every time you need that index because you have to join the two tables for some reason on the referring field. Using the code So we have to make 2 stored procedures. The first one should drop all the foreign key constraints before dropping the referred table. So here is what I do step by step 1. I create temp table #statements which I need to be filled with drop statements which I will execute row by row later. 2. I extract all the referring constraints and tables and create statements which I insert in the temp table. 3. I execute the statements one by one using the cursor 4. Clean all the temp stuff I made and all the cursors

if OBJECT_ID('_p_drop_all_foreign_keys') is not null 
DROP PROCEDURE _p_drop_all_foreign_keys 
go
--_p_drop_all_foreign_keys 'COURTS'
CREATE PROCEDURE _p_drop_all_foreign_keys @tableName nvarchar (max) as 
BEGIN
CREATE TABLE #statements 
( 
statement nvarchar (max) 
) 
;WITH ForeignKeyInfo as 
( 
SELECT 
OBJECT_NAME(f.parent_object_id) AS TableName, 
f
.name as fkname, 
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName 
FROM sys.foreign_keys AS f 
WHERE OBJECT_NAME (f.referenced_object_id) = @tableName 
) 
INSERT INTO #statements (statement) 
SELECT 
'alter TABLE '+TableName+' DROP constraint '+fkname 
FROM ForeignKeyInfo 
DECLARE @q nvarchar (max) 
DECLARE crsStatements cursor for SELECT statement FROM #statements 
OPEN crsStatements 
FETCH next FROM crsStatements INTO @q 
WHILE @@FETCH_STATUS=0 
BEGIN 
EXECUTE (@q) 
FETCH next FROM crsStatements INTO @q 
END 
CLOSE crsStatements 
DEALLOCATE crsStatements 
DROP TABLE #statements 
END
go

The second one should create all the indices 1. I create temp table #statements which I need to be filled with drop statements which I will execute row by row later. 2. I extract all the fields with foreign key constraints and create statements which I insert in the temp table. 3. I execute the statements one by one using the cursor 4. Clean all the temp stuff I made and all the cursors

IF OBJECT_ID('_p_create_fk_indeces') IS NOT NULL
DROP PROCEDURE _p_create_fk_indeces
GO
-- _p_create_fk_indexes 'COURTS'
CREATE PROCEDURE _p_create_fk_indexes @tableName nvarchar (max) AS
BEGIN
CREATE TABLE #statements 
( 
statement nvarchar (max) 
) 
;WITH ForeignKeyInfo as 
( 
SELECT 
OBJECT_NAME(f.parent_object_id) AS TableName, 
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName, 
'idx_'+OBJECT_NAME(f.parent_object_id)+'_'+COL_NAME(fc.parent_object_id, fc.parent_column_id) IndexName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id 
WHERE OBJECT_NAME (f.parent_object_id) = @tableName 
) 
INSERT INTO #statements (statement) 
SELECT 
'if exists (SELECT * FROM sysindexes WHERE id=object_id('''+TableName+''') and name='''+Indexname+''') '+ 
'DROP index '+indexname+' on '+tablename+'; '+ 
'CREATE index '+indexname+' on '+tablename+'('+columnname+'); ' FROM ForeignKeyInfo 

DECLARE @q nvarchar (max) 
DECLARE crsStatements cursor for SELECT statement FROM #statements 
OPEN crsStatements 
FETCH next FROM crsStatements INTO @q 
WHILE @@FETCH_STATUS=0 
BEGIN 
--print @q
EXECUTE (@q) 
FETCH next FROM crsStatements INTO @q 
END 
CLOSE crsStatements 
DEALLOCATE crsStatements 
DROP TABLE #statements 
END
go

To be honest, some parts of the code are not mine. I copied them from another folk’s website longtime ago and sadly I cannot recall his name, otherwise I would have written it down here…