July 5, 2011 at 8:19 am
This article helps one to create SSIS POC for Drop & Create constraints and indexes dynamically. This method can be applied to any SSIS package to improve the performance. I applied this method to my SSIS package and found it more useful. In this article, you can find script to create stored procedures and tables to arrive at solution.
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
----------------------------------------------
You can call these Stored Procedures in Execute SQL Task in your SSIS Package, i.e., create a Sequence container and Executive SQL Task to drop all PKs, FKs, and indexes, then continue with your Data Flow task and insert data to the target table, then create a sequence container and add Execute SQL task to rebuild PKs, FKs, and indexes for the table.
Refer: http://mahadevanrv.blogspot.com/2011/07/i-managed-to-create-ssis-poc-for-drop.html for image
July 5, 2011 at 8:52 am
If you have to drop PK's, FK's, and/or Indexes for ETL, then you're doing something drastically wrong.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2011 at 8:55 am
I would recommend testing this approach before anyone goes out and does it, there are mixed opionions on if dropping and re-creating indexes for an ETL load is the best method.
also as a side note, if you wrap your code in the appropriate tags it makes it a lot more readable..
July 5, 2011 at 9:36 am
Dropping nonclustered indexes I would agree with. There's a point where it is quicker to drop, load and recreate than to load with the indexes.
Dropping foreign key and primary key however.... That's a really bad idea. Not only are you rising data integrity problems (are your SSIS packages really bug free? Is the source data really perfect?) but dropping and recreating the clustered index can take significant amounts of time on larger tables (hours or even more)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2011 at 10:34 am
Mahadevan,
This has worked out very well for me. It enabled me to pull data from a production database and keep all the original keys when it was sent to the development database. Otherwise, when I inserted the data into the development database, it was messing up the keys.
However, now when I run the code, I am getting the following error:
[Execute SQL Task] Error: Executing the query "Exec spDropAllIndexes Dates" failed with the following error: "An explicit DROP INDEX is not allowed on index 'Dates.PK_Dates'. It is being used for PRIMARY KEY constraint enforcement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas what is causing this?
Thank you.
September 15, 2011 at 10:51 am
Rob Goodman (9/15/2011)
Mahadevan,However, now when I run the code, I am getting the following error:
[Execute SQL Task] Error: Executing the query "Exec spDropAllIndexes Dates" failed with the following error: "An explicit DROP INDEX is not allowed on index 'Dates.PK_Dates'. It is being used for PRIMARY KEY constraint enforcement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas what is causing this?
Concerning your error, you can't drop an index that is being used by a primary key constraint.
As you were previously advised dropping PK and FK Constraints is bad and If you have to do that you are doing something wrong.
Remove the code to drop the constraints.
Always load the Data from the Child (lowest Level) to the parent table.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 15, 2011 at 11:12 am
Welsh Corgi,
Thank you for the information. I understand that dropping the keys means that something I'm doing is wrong. However, when I am transferring data from one database to another and I need to keep all the keys used in the first database, what is the best way to do that without messing up the keys?
For example, I have a Company table and a Product table — both have Primary keys. The Product table also has a foreign key of the Company table.
Company Table
1 Joe's Shoes
2 Bob's Hats
Product Table
ID Company ID Product
1 1 Boots
2 1 Galoshes
3 1 Pumps
4 2 Caps
5 2 Fedora
Now if I am going to pull in the data from scratch, and the primary keys are already set, when I import the Company table, the keys are going to increase (as would be expected) and then the CompanyID on the Product tables would no longer link to the Company table.
Granted, I am not going to do this for any ETL processes and this is only for me to pull in data for testing. Although, in "The Data Warehouse ETL Toolkit" Ralph Kimball talks about removing indexes and rebuilding them for loading purposes (but that is an index and not a key).
Thanks for listening. I am just looking for the best approach on this. I appreciate the advice and help.
September 15, 2011 at 3:13 pm
All you need to do is load the Product Table before the Company Table since the Product Table is a Child to the Company Table (FK Relationship).
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 15, 2011 at 3:24 pm
Sorry, double post.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 25, 2011 at 2:34 am
Thank you for trying the code,
I have modified the stored procedures to resolve few issues, the updated stored procedures are available in below link:
http://www.4shared.com/document/kPzTN1on/Drop_Rebuild_Constraints.html
Also we must execute the stored procedure in following Sequence:
1. Drop Reference Keys
2. Drop Foreign Keys
3. Drop Primary Key
4. Drop Indexes
5. Rebuild Primary Key
6. ReBuild Foreign Key
7. Rebuild Reference Foreign Key
8. Rebuild Indexes.
Before executing the above process, please make sure you execute stored procedures to insert contraints and index details to appropriate tables.
October 25, 2011 at 6:03 am
First of all. I completely agree with Gail, dropping and recreating a clustred index may be a very costly operation. Furthermore, I don't like the idea of dropping constraints during load either. After all, it is a reason why you do have primary key, foreign key, and unique keys/indexes, but I do realize that you can verfify the data intgrity at the end of the load.
As for your order, as far as possible, you should drop all nonclustered indexs first, then the clustered index. Dropping a clustered index will cause rebuild of all nonclustered indexes. Similarily, you should create your clustered index first, then all nonclustered. As creating a clustered index also will cause rebuild of all non clustered indexes.
October 25, 2011 at 6:19 am
I agree, you should not have to drop the FK constraints as you want to maintain the integrity. And I am not sure the performance gain would be enough to justify losing RI.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply