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…
