August 29, 2013 at 5:58 am
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
-----------------------------------------------------------------------------------
August 29, 2013 at 7:33 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2013 at 8:45 am
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!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply