I managed to create SSIS POC for Drop & Create constraints and indexes dynamically.
I have created the following tables and stored procedures to arrive at the solution:
Two tables to store Index and Constraints details:
1. dbo.TableConstraints: This table holds the detail of all constraints (PK's & FK's) created in a database.
2. dbo.TableIndex: This table contains the detail of all indexes created in a database.
Nine stored procedures to drop and create:
1. spInsertConstraints : Execute this procedure to populate 'dbo.TableConstraints', when you make any changes to table or database structure this has to be executed.
2. spInsertIndexes: Execute this procedure to populate 'dbo.TableIndex', when you make any changes to table or database structure this has to be executed.
3. spDropRefFKs: Drops all reference relationship in a table.
4. spDropPKFKConstarints: Drops all constraints in a table.
5. spDropAllIndexes: Drops all indexes in a table.
6. spBuildPKConstarints: Rebuilds PK in atable (gets details from 'dbo.TableConstarints').
7. spBuildFKConstarints: Rebuilds all Foreign Keys in a table (gets details from 'dbo.TableConstarints').
8. spBuildFKRefs: Rebuilds all reference relationship in a table (gets details from 'dbo.TableConstarints').
9. spBuildAllIndexes: Rebuilds all indexes in a table (gets index details from 'dbo.TableIndex').
Initial steps to be followed:
1. Execute stored procedure 'spInsertConstraints'. It is one time run until there is no changes in database. If you made any changes to database or tables, this has to be re-executed.
2. Execute stored procedure 'spInsertIndexes'. It is one time run until there is no changes in database. If you made any changes to database or tables, this has to be re-executed.
Script for Stored Procedure
USE [ListSelect20_be]
GO
/****** Object: Table [dbo].[TableIndex] Script Date: 07/05/2011 12:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableIndex](
[ObjectID] [bigint] NULL,
[TableName] [varchar](100) NULL,
[SchemaName] [varchar](50) NULL,
[IndexName] [varchar](100) NULL,
[IndexID] [int] NULL,
[IndexType] [int] NULL,
[IXDescription] [varchar](100) NULL,
[ColumnName] [varchar](100) NULL,
[IsUnique] [varchar](50) NULL,
[IsUniqueConstraint] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TableConstraints] Script Date: 07/05/2011 12:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableConstraints](
[ObjectID] [bigint] NULL,
[Constraint_Catalog] [varchar](100) NULL,
[Constraint_Schema] [varchar](100) NULL,
[Constraint_Name] [varchar](100) NULL,
[Table_Catalog] [varchar](100) NULL,
[Table_Schema] [varchar](100) NULL,
[Table_name] [varchar](100) NULL,
[Constraint_Type] [varchar](100) NULL,
[Is_Deferrable] [nchar](10) NULL,
[Initially_Deferrable] [nchar](10) NULL,
[Column_Name] [varchar](100) NULL,
[Ordinal_Position] [int] NULL,
[TypeDesc] [varchar](100) NULL,
[Is_Unique] [varchar](50) NULL,
[Is_Unique_Constraint] [varchar](50) NULL,
[ParentObjectID] [bigint] NULL,
[ParentObject] [varchar](100) NULL,
[ReferenceObjectID] [bigint] NULL,
[ReferenceObject] [varchar](100) NULL,
[ReferenceColumnID] [bigint] NULL,
[ReferenceColumn] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[spDropPKFKConstarints] Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ========================================================
-- Author : Mahadevan
-- Create date: June 23, 2011
-- Description: Remove all constraints PK and FK in a table
-- =========================================================
CREATE PROCEDURE [dbo].[spDropPKFKConstarints]
@Database VARCHAR(50),
@Schema VARCHAR(50),
@Tablename VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN
DECLARE @SQL NVARCHAR(255)
WHILE EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_CATALOG = @Database
AND TABLE_NAME = @Tablename AND CONSTRAINT_SCHEMA = @Schema)
BEGIN
SELECT @SQL = 'ALTER TABLE ' + @Tablename + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_CATALOG = @Database AND TABLE_NAME = @Tablename AND CONSTRAINT_SCHEMA = @Schema
ORDER BY CONSTRAINT_TYPE DESC
EXEC sp_executesql @sql
END
END
END
GO
/****** Object: StoredProcedure [dbo].[spDropAllIndexes] Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================
-- Author: Mahadevan
-- Create date: 26/6/2011
-- Description: To drop all the indexes in a table
-- =================================================
CREATE PROCEDURE [dbo].[spDropAllIndexes]
@Tablename VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @IndexName VARCHAR(128)
DECLARE @Table VARCHAR(128)
DECLARE [GetIndexes] CURSOR FOR
SELECT I.[name] AS [Index],
S.[name] AS [Table]
FROM [sysindexes] I
INNER JOIN [sysobjects] S
ON I.[id] = S.[id]
WHERE I.[name] IS NOT NULL
AND S.[type] = 'U'
AND I.INDID > 0
AND I.INDID < 255
AND (I.STATUS & 64)=0
AND S.[Name] = @Tablename
ORDER BY [Table]
OPEN [GetIndexes]
FETCH NEXT FROM [GetIndexes] INTO @IndexName, @Table
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(255)
BEGIN
SELECT @SQL = 'DROP INDEX [' + @IndexName + '] ON [' + @Table + '] '
+ 'WITH ( ONLINE = OFF );'
END
--PRINT @SQL
EXEC sp_executesql @SQL
FETCH NEXT FROM [GETIndexes] INTO @IndexName, @Table
END
CLOSE [GetIndexes]
DEALLOCATE [GetIndexes]
END
GO
/****** Object: StoredProcedure [dbo].[spBuildPKConstarints] Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ========================================================
-- Author : Mahadevan
-- Create date: June 24, 2011
-- Description: Create PK in a table
-- =========================================================
CREATE PROCEDURE [dbo].[spBuildPKConstarints]
@Tablename VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN
DECLARE @Table VARCHAR(50)
DECLARE @ConstraintName VARCHAR(50)
DECLARE @ConstraintDesc VARCHAR(50)
DECLARE @ConstraintColumn1 VARCHAR(50)
DECLARE @ConstraintColumn2 VARCHAR(50)
DECLARE [GetConstraints] CURSOR FOR
SELECT A.TABLE_NAME
,A.CONSTRAINT_NAME
,A.TYPEDESC
,A.COLUMN_NAME AS Column1
,B.Column_Name AS Column2
FROM TableConstraints A
LEFT JOIN (SELECT OBJECTID, Column_Name FROM TableConstraints WHERE Ordinal_Position = 2) B ON B.OBJECTID = A.ObjectID
WHERE A.Ordinal_Position=1 AND A.Table_name = @Tablename AND A.Constraint_Type = 'PRIMARY KEY'
OPEN [GetConstraints]
FETCH NEXT FROM [GetConstraints] INTO @Table, @ConstraintName, @ConstraintDesc, @ConstraintColumn1, @ConstraintColumn2
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(1000)
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = @Table
AND CONSTRAINT_NAME = @ConstraintName)
BEGIN
IF (@ConstraintColumn2 IS NULL)
SELECT @SQL = 'ALTER TABLE ' + @Table + ' ADD CONSTRAINT '+ @ConstraintName
+ ' PRIMARY KEY ' + @ConstraintDesc + '(' + @ConstraintColumn1 + ' ASC)'
+ ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF'
+ ',SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF'
+ ',ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];'
ELSE
SELECT @SQL = 'ALTER TABLE ' + @Table + ' ADD CONSTRAINT '+ @ConstraintName
+ ' PRIMARY KEY ' + @ConstraintDesc + '(' + @ConstraintColumn1 +','+ @ConstraintColumn2+')'
+ ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF'
+ ',SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF'
+ ',ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];'
END
--Print @SQL
EXEC sp_executesql @SQL
FETCH NEXT FROM [GetConstraints] INTO @Table, @ConstraintName, @ConstraintDesc, @ConstraintColumn1, @ConstraintColumn2
END
CLOSE [GetConstraints]
DEALLOCATE [GetConstraints]
END
END
GO
/****** Object: StoredProcedure [dbo].[spBuildFKRefs] Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author: Mahadevan
-- Create date: 26/6/2011
-- Description: To rebuild all the FK's in a table
-- ======================================================
CREATE PROCEDURE [dbo].[spBuildFKRefs]
@Tablename VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN
DECLARE @Table VARCHAR(128)
DECLARE @ConstraintName VARCHAR(128)
DECLARE @ColumnName VARCHAR(128)
DECLARE @ReferenceObject VARCHAR(128)
DECLARE @ReferenceColumn VARCHAR(128)
DECLARE [GetConstraints] CURSOR FOR
SELECT Table_name
,Constraint_Name
,Column_Name
,ReferenceObject
,ReferenceColumn
FROM TableConstraints
WHERE ReferenceObject = @Tablename AND Constraint_Type = 'FOREIGN KEY'
OPEN [GetConstraints]
FETCH NEXT FROM [GetConstraints] INTO @Table, @ConstraintName, @ColumnName, @ReferenceObject, @ReferenceColumn
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(255)
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(@ConstraintName)
AND parent_object_id = OBJECT_ID(@Table))
BEGIN
SELECT @SQL = 'ALTER TABLE ' + @Table + ' WITH CHECK ADD CONSTRAINT ' + @ConstraintName
+ ' FOREIGN KEY ('+ @ColumnName +') REFERENCES '+ @ReferenceObject
+ ' ('+ @ReferenceColumn +') ON UPDATE NO ACTION ON DELETE NO ACTION'
END
--PRINT @SQL
EXEC sp_executesql @SQL
FETCH NEXT FROM [GetConstraints] INTO @Table, @ConstraintName, @ColumnName, @ReferenceObject, @ReferenceColumn
END
CLOSE [GetConstraints]
DEALLOCATE [GetConstraints]
END
END
GO
/****** Object: StoredProcedure [dbo].[spBuildFKConstarints] Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author: Mahadevan
-- Create date: 26/6/2011
-- Description: To rebuild all the FK's in a table
-- ======================================================
CREATE PROCEDURE [dbo].[spBuildFKConstarints]
@Tablename VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN
DECLARE @Table VARCHAR(128)
DECLARE @ConstraintName VARCHAR(128)
DECLARE @ColumnName VARCHAR(128)
DECLARE @ReferenceObject VARCHAR(128)
DECLARE @ReferenceColumn VARCHAR(128)
DECLARE [GetConstraints] CURSOR FOR
SELECT Table_name
,Constraint_Name
,Column_Name
,ReferenceObject
,ReferenceColumn
FROM TableConstraints
WHERE Table_Name = @Tablename AND Constraint_Type = 'FOREIGN KEY'
OPEN [GetConstraints]
FETCH NEXT FROM [GetConstraints] INTO @Table, @ConstraintName, @ColumnName, @ReferenceObject, @ReferenceColumn
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(255)
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(@ConstraintName)
AND parent_object_id = OBJECT_ID(@Table))
BEGIN
SELECT @SQL = 'ALTER TABLE ' + @Table + ' WITH CHECK ADD CONSTRAINT ' + @ConstraintName
+ ' FOREIGN KEY ('+ @ColumnName +') REFERENCES '+ @ReferenceObject
+ ' ('+ @ReferenceColumn +') ON UPDATE NO ACTION ON DELETE NO ACTION'
END
--PRINT @SQL
EXEC sp_executesql @SQL
FETCH NEXT FROM [GetConstraints] INTO @Table, @ConstraintName, @ColumnName, @ReferenceObject, @ReferenceColumn
END
CLOSE [GetConstraints]
DEALLOCATE [GetConstraints]
END
END
GO
/****** Object: StoredProcedure [dbo].[spBuildAllIndexes] Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================
-- Author: Mahadevan
-- Create date: 26/6/2011
-- Description: To create all the indexes in a table
-- =================================================
CREATE PROCEDURE [dbo].[spBuildAllIndexes]
@Tablename VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Table VARCHAR(50)
DECLARE @IndexName VARCHAR(50)
DECLARE @Type VARCHAR(50)
DECLARE @Column1 VARCHAR(50)
DECLARE @Column2 VARCHAR(50)
DECLARE @Column3 VARCHAR(50)
DECLARE @Column4 VARCHAR(50)
DECLARE @Unique VARCHAR(50)
DECLARE [GetIndexes] CURSOR FOR
SELECT T1.Tablename
,T1.IndexName
,T1.IXDescription
,T1.ColumnName AS Column1
,T2.ColumnName AS Column2
,T3.ColumnName AS Column3
,T4.ColumnName AS Column4
,T1.IsUnique
FROM (SELECT RANK() OVER (Order BY ColumnName ASC) AS SNO
,ObjectID
,Tablename
,IndexName
,IXDescription
,ColumnName
,IsUnique
FROM TableIndex
WHERE TableName = @Tablename AND IXDescription = 'NONCLUSTERED') T1
LEFT JOIN (SELECT RANK() OVER (ORDER BY ColumnName ASC) AS SNO, ObjectID, ColumnName FROM TableIndex
WHERE TableName = @Table AND IXDescription = 'NONCLUSTERED') T2 ON T2.ObjectID = T1.ObjectID AND T2.SNO = T1.Sno +1
LEFT JOIN (SELECT RANK() OVER (ORDER BY ColumnName ASC) AS SNO, ObjectID, ColumnName FROM TableIndex
WHERE TableName = @Table AND IXDescription = 'NONCLUSTERED') T3 ON T3.ObjectID = T2.ObjectID AND T3.SNO = T2.Sno +1
LEFT JOIN (SELECT RANK() OVER (ORDER BY ColumnName ASC) AS SNO, ObjectID, ColumnName FROM TableIndex
WHERE TableName = @Table AND IXDescription = 'NONCLUSTERED') T4 ON T4.ObjectID = T3.ObjectID AND T4.SNO = T3.Sno +1
WHERE T1.SNO = 1
OPEN [GetIndexes]
FETCH NEXT FROM [GetIndexes] INTO @Table, @IndexName, @Type, @Column1, @Column2, @Column3, @Column4, @Unique
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(1000)
IF NOT EXISTS (SELECT * from sys.indexes WHERE Name = (@IndexName)
AND object_id = OBJECT_ID(@Table))
BEGIN
IF (@Column2 IS NULL)
BEGIN
SELECT @SQL = 'CREATE '
+@Unique+ ' '+ @Type + ' INDEX [' + @IndexName + '] ON [' + @Table + '] '
+'(['+@Column1+'] ASC)'
+ 'WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,'
+ 'IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];'
END
IF (@Column2 IS NOT NULL AND @Column3 IS NULL)
BEGIN
SELECT @SQL = 'CREATE '
+@Unique+ ' '+ @Type + ' INDEX [' + @IndexName + '] ON [' + @Table + '] '
+'(
'+@Column1+','+@Column2+')'
+ 'WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,'
+ 'IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];'
END
IF (@Column2 IS NOT NULL AND @Column3 IS NOT NULL AND @Column4 IS NULL)
BEGIN
SELECT @SQL = 'CREATE '
+@Unique+ ' '+ @Type + ' INDEX [' + @IndexName + '] ON [' + @Table + '] '
+'(
'+@Column1+','+@Column2+','+@Column3+')'
+ 'WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,'
+ 'IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];'
END
ELSE
BEGIN
SELECT @SQL = 'CREATE '
+@Unique+ ' '+ @Type + ' INDEX [' + @IndexName + '] ON [' + @Table + '] '
+'(
'+@Column1+','+@Column2+','+@Column3+','+@Column4+')'
+ 'WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,'
+ 'IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];'
END
END
--Print @SQL
EXEC SP_EXECUTESQL @SQL
FETCH NEXT FROM [GETIndexes] INTO @Table, @IndexName, @Type, @Column1, @Column2, @Column3, @Column4, @Unique
END
CLOSE [GetIndexes]
DEALLOCATE [GetIndexes]
END
GO
/****** Object: StoredProcedure [dbo].[spInsertIndexes] Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================================================
-- Author: Mahadevan
-- Create date: June 22, 2011
-- Description: To insert all indexes used in a database to table Tableindex
-- ====================================================================================
CREATE PROCEDURE [dbo].[spInsertIndexes]
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE dbo.Tableindex
BEGIN
INSERT INTO [dbo].[TableIndex]
(ObjectID
,TableName
,SchemaName
,IndexName
,IndexID
,IndexType
,IXDescription
,ColumnName
,IsUnique
,IsUniqueConstraint)
(SELECT I.OBJECT_ID
,T.NAME TABLE_NAME
,S.NAME SCHEMA_NAME
,I.NAME INDEX_NAME
,I.index_id INDEX_ID
,I.type INDEX_TYPE
,I.type_desc AS IndexDescription
,C.NAME COLUMN_NAME
,CASE I.is_unique WHEN 1 Then 'UNIQUE' ELSE '' END AS IS_UNIQUE
,CASE I.is_unique_constraint WHEN 1 Then 'UNIQUE CONSTRAINT' ELSE '' END AS IS_UNIQUE_CONSTRAINT
FROM SYS.TABLES T
INNER JOIN SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID
INNER JOIN SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = T.OBJECT_ID
INNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = T.OBJECT_ID
AND IC.INDEX_ID = I.INDEX_ID AND IC.COLUMN_ID = C.COLUMN_ID
WHERE 1=1 AND I.type = 2 AND (S.name = 'dbo')
)
END
END
GO
/****** Object: StoredProcedure [dbo].[spInsertConstraints] Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================================================
-- Author: Mahadevan
-- Create date: June 22, 2011
-- Description: To insert all constarints used in a database to table TableConstraints
-- ====================================================================================
CREATE PROCEDURE [dbo].[spInsertConstraints]
@Database Varchar(50),
@Schema Varchar(50)
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE dbo.TableConstraints
BEGIN
INSERT INTO dbo.TableConstraints
(ObjectID
,Constraint_Catalog
,Constraint_Schema
,Constraint_Name
,Table_Catalog
,Table_Schema
,Table_name
,Constraint_Type
,Is_Deferrable
,Initially_Deferrable
,Column_Name
,Ordinal_Position
,TypeDesc
,Is_Unique
,Is_Unique_Constraint
,ParentObjectID
,ParentObject
,ReferenceObjectID
,ReferenceObject
,ReferenceColumnID
,ReferenceColumn)
(SELECT Distinct SO.Object_ID
,ISTC.Constraint_Catalog
,ISTC.Constraint_Schema
,ISTC.Constraint_Name
,ISTC.Table_Catalog
,ISTC.Table_Schema
,ISTC.Table_name
,ISTC.Constraint_Type
,ISTC.Is_Deferrable
,ISTC.Initially_Deferred
,ISKC.Column_Name
,ISKC.Ordinal_Position
,SI.type_desc
,CASE WHEN SI.is_unique = 1 THEN 'UNIQUE' ELSE '' END
,CASE WHEN SI.is_unique_constraint= 1 THEN 'UNIQUE' ELSE '' END
,SO.Parent_object_ID
,PO.Name AS ParentObject
,SFK.referenced_object_id
,RO.NAME AS ReferenceObject
,FKC.referenced_column_id
,SC.name AS Ref_column
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ISTC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ISKC ON ISKC.CONSTRAINT_NAME = ISTC.CONSTRAINT_NAME
AND ISKC.CONSTRAINT_CATALOG = ISTC.CONSTRAINT_CATALOG
AND ISKC.TABLE_NAME =ISTC.TABLE_NAME
INNER JOIN Sys.Objects SO ON SO.Name = ISTC.CONSTRAINT_NAME
LEFT JOIN Sys.indexes SI ON SI.name = ISTC.CONSTRAINT_NAME
LEFT JOIN sys.foreign_keys SFK ON SFK.object_id = SO.object_id
LEFT JOIN (SELECT name, object_id from sys.objects) PO ON PO.OBJECT_ID = SO.Parent_Object_ID
LEFT JOIN (SELECT name, object_id from sys.objects) RO ON RO.OBJECT_ID = SFK.referenced_object_id
LEFT JOIN sys.foreign_key_columns FKC ON FKC.referenced_object_id = SFK.referenced_object_id
LEFT JOIN SYSCOLUMNS SC ON SC.id = SFK.referenced_object_id AND SC.colid = FKC.referenced_column_id
WHERE ISTC.CONSTRAINT_CATALOG = @Database AND ISTC.CONSTRAINT_SCHEMA = @Schema)
END
END
GO
/****** Object: StoredProcedure [dbo].[spDropRefFKs] Script Date: 07/05/2011 12:32:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author: Mahadevan
-- Create date: 26/6/2011
-- Description: To drop all the Reference FK's in a table
-- ======================================================
CREATE PROCEDURE [dbo].[spDropRefFKs]
@Tablename VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN
DECLARE @Table VARCHAR(128)
DECLARE @ReferenceObject VARCHAR(100)
DECLARE @ConstraintName VARCHAR(128)
DECLARE [GetRefs] CURSOR FOR
SELECT Table_name,
ReferenceObject,
Constraint_Name
FROM TableConstraints
WHERE ReferenceObject = @Tablename
ORDER BY [Table_Name]
OPEN [GetRefs]
FETCH NEXT FROM [GetRefs] INTO @Table, @ReferenceObject, @ConstraintName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(255)
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = (@ConstraintName)
AND referenced_object_id = OBJECT_ID(@ReferenceObject))
BEGIN
SELECT @SQL = 'ALTER TABLE ' + @Table + ' DROP CONSTRAINT [' + @ConstraintName + '];'
END
--PRINT @SQL
EXEC sp_executesql @SQL
FETCH NEXT FROM [GetRefs] INTO @Table, @ReferenceObject, @ConstraintName
END
CLOSE [GetRefs]
DEALLOCATE [GetRefs]
END
END
GO
----------------------------------------------
Now design your package as shown below and use appropriate stored procedures in Execute SQL Task.
