Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Drop Constraints, Truncate and Re-Create Constraints Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 5:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 29, 2013 10:25 PM
Points: 9, Visits: 51
Hi,

I am working on a SP that for a given database;

1. Drops the referential integrity constraints.
2. Truncates the tables.
3. Re-creates the integrity constraints.

I started testing the code and the code seems to drop the constraints well but it kind of bugs while Truncating the tables.

I am using INFORMATION_SCHEMA to generate the temp table of table's constraint list, the dynamic code generation for Truncation seems to fail.

I desperately need to fix this on priority.

Here is the code:

-----------------------------------------------------------------------------------
CREATE PROCEDURE usp_TruncateTableData_ConstraintDropAdd
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@FK_TableSchema NVARCHAR(200),
@FK_TableName NVARCHAR(200),
@FK_Name NVARCHAR(200),
@FK_ColumnName NVARCHAR(200),
@PK_TableSchema NVARCHAR(200),
@PK_TableName NVARCHAR(200),
@PK_ColumnName NVARCHAR(200),
@DROP VARCHAR(MAX),
@ADD VARCHAR(MAX),
@TRUNC VARCHAR(MAX)

DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table

---------------------------------------------------------------------------------------------------------
--DROP CONSTRAINT:

DECLARE FK_DROP CURSOR FOR
SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintName
FROM @table
OPEN FK_DROP
FETCH NEXT FROM FK_DROP INTO @FK_TableSchema, @FK_TableName, @FK_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DROP = 'ALTER TABLE [' + @FK_TableSchema + '].[' + @FK_TableName + '] DROP CONSTRAINT ' + @FK_Name

EXECUTE(@DROP)
--PRINT @DROP

FETCH NEXT FROM FK_DROP INTO @FK_TableSchema, @FK_TableName, @FK_Name
END
CLOSE FK_DROP
DEALLOCATE FK_DROP

---------------------------------------------------------------------------------------------------------
--TRUNCATE TABLES:

DECLARE FK_TRUNC CURSOR FOR
SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName
FROM @table
OPEN FK_TRUNC
FETCH NEXT FROM FK_TRUNC INTO @FK_TableSchema, @FK_TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TRUNC = 'TRUNCATE TABLE [' + @FK_TableName + ']'

EXECUTE(@TRUNC)
--PRINT @TRUNC

FETCH NEXT FROM FK_TRUNC INTO @FK_TableSchema, @FK_TableName
END
CLOSE FK_TRUNC
DEALLOCATE FK_TRUNC

---------------------------------------------------------------------------------------------------------
--ADD CONSTRAINT:

DECLARE FK_ADD CURSOR FOR
SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintName, ForeignKeyConstraintColumnName,
PrimaryKeyConstraintTableSchema, PrimaryKeyConstraintTableName, PrimaryKeyConstraintColumnName
FROM @table
OPEN FK_ADD
FETCH NEXT FROM FK_ADD INTO @FK_TableSchema, @FK_TableName, @FK_Name, @FK_ColumnName, @PK_TableSchema, @PK_TableName, @PK_ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ADD = 'ALTER TABLE [' + @FK_TableSchema + '].[' + @FK_TableName + '] ADD CONSTRAINT ' + @FK_Name +
' FOREIGN KEY(' + @FK_ColumnName + ') REFERENCES [' + @PK_TableSchema + '].[' + @PK_TableName + '](' + @PK_ColumnName + ')'

EXECUTE(@ADD)
--PRINT @ADD

FETCH NEXT FROM FK_ADD INTO @FK_TableSchema, @FK_TableName, @FK_Name, @FK_ColumnName, @PK_TableSchema, @PK_TableName, @PK_ColumnName
END
CLOSE FK_ADD
DEALLOCATE FK_ADD

---------------------------------------------------------------------------------------------------------

END
GO
-----------------------------------------------------------------------------------
Post #1489659
Posted Thursday, August 29, 2013 7:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 13,008, Visits: 12,427
Why do you need to drop every foreign key, then truncate every table frequently enough that you need a procedure for this?

We can probably help but what does "I started testing the code and the code seems to drop the constraints well but it kind of bugs while Truncating the tables." mean? Can you provide some details about what happens or doesn't happen that you think should?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1489720
Posted Friday, August 30, 2013 8:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:19 AM
Points: 51, Visits: 616
First of all, this is quite a coincidence. I just happened to be getting ready to do exactly the same thing. I have to repeatedly drop the FKs, truncate tables, and re-add the FKs. I just have to pass the Database name to the procedure as I will be doing this for 100s of DBs.

FYI - these are copies of DBs that have to have some sensitive data removed before using the copies. Sort of a test environment.

Anyway, I am able to get your proc to work by adding the table schema to the truncate table command. I don't know if you have the same error/situation, but that is what got it working for me.

Thanks for submitting this code and your issue. I hope this also resolves your issue. If I can use your proc it saves me a lot of time, I thought I would be writing this from scratch today!!!
Post #1490191
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse