Truncate a Table that Has Foreign Keys in SQL Server

-
/* borrowed 20150717 by wills - from pszanto at http://stackoverflow.com/a/13249209/377058 - made @Debug a parameter */ ALTER PROCEDURE [dbo].[usp_Admin_TruncateNonEmptyTable] @TableToTruncate varchar(64), @Debug bit = 1 AS BEGIN SET NOCOUNT ON -- GLOBAL VARIABLES DECLARE @i int --DECLARE @Debug bit DECLARE @Recycle bit DECLARE @Verbose bit DECLARE @TableName varchar(80) DECLARE @ColumnName varchar(80) DECLARE @ReferencedTableName varchar(80) DECLARE @ReferencedColumnName varchar(80) DECLARE @ConstraintName varchar(250) DECLARE @CreateStatement varchar(max) DECLARE @DropStatement varchar(max) DECLARE @TruncateStatement varchar(max) DECLARE @CreateStatementTemp varchar(max) DECLARE @DropStatementTemp varchar(max) DECLARE @TruncateStatementTemp varchar(max) DECLARE @Statement varchar(max) -- 1 = Will not execute statements --SET @Debug = 0 if @Debug = 1 begin print 'Debug is on, no Truncate will actually occur.' end else if @Debug = 0 begin print 'Debug is on, this is the real deal. Here we go...' end
-- 0 = Will not create or truncate storage table -- 1 = Will create or truncate storage table SET @Recycle = 0 -- 1 = Will print a message on every step set @Verbose = 1 SET @i = 1 SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])' SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]' SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]' -- Drop Temporary tables IF OBJECT_ID('tempdb..#FKs') IS NOT NULL DROP TABLE #FKs -- GET FKs SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID, OBJECT_NAME(constraint_object_id) as ConstraintName, OBJECT_NAME(parent_object_id) as TableName, clm1.name as ColumnName, OBJECT_NAME(referenced_object_id) as ReferencedTableName, clm2.name as ReferencedColumnName INTO #FKs FROM sys.foreign_key_columns fk JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id AND fk.parent_object_id = clm1.object_id JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id AND fk.referenced_object_id= clm2.object_id --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated') WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate ORDER BY OBJECT_NAME(parent_object_id) -- Prepare Storage Table IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage') BEGIN IF @Verbose = 1 PRINT '1. Creating Process Specific Tables...' -- CREATE STORAGE TABLE IF IT DOES NOT EXISTS CREATE TABLE [Internal_FK_Definition_Storage] ( ID int not null identity(1,1) primary key, FK_Name varchar(250) not null, FK_CreationStatement varchar(max) not null, FK_DestructionStatement varchar(max) not null, Table_TruncationStatement varchar(max) not null ) END ELSE BEGIN IF @Recycle = 0 BEGIN IF @Verbose = 1 PRINT '1. Truncating Process Specific Tables...' -- TRUNCATE TABLE IF IT ALREADY EXISTS TRUNCATE TABLE [Internal_FK_Definition_Storage] END ELSE PRINT '1. Process specific table will be recycled from previous execution...' END IF @Recycle = 0 BEGIN IF @Verbose = 1 PRINT '2. Backing up Foreign Key Definitions...' -- Fetch and persist FKs WHILE (@i <= (SELECT MAX(ID) FROM #FKs)) BEGIN SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i) SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i) SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i) SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i) SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i) SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName) SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName) SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName) INSERT INTO [Internal_FK_Definition_Storage] SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp SET @i = @i + 1 IF @Verbose = 1 PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']' END END ELSE PRINT '2. Backup up was recycled from previous execution...' IF @Verbose = 1 PRINT '3. Dropping Foreign Keys...' -- DROP FOREING KEYS SET @i = 1 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) BEGIN SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i) SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i) IF @Debug = 1 PRINT @Statement ELSE EXEC(@Statement) SET @i = @i + 1 IF @Verbose = 1 PRINT ' > Dropping [' + @ConstraintName + ']' END IF @Verbose = 1 PRINT '4. Truncating Tables...' -- TRUNCATE TABLES -- SzP: commented out as the tables to be truncated might also contain tables that has foreign keys -- to resolve this the stored procedure should be called recursively, but I dont have the time to do it... /* SET @i = 1 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) BEGIN SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i) IF @Debug = 1 PRINT @Statement ELSE EXEC(@Statement) SET @i = @i + 1 IF @Verbose = 1 PRINT ' > ' + @Statement END */ IF @Verbose = 1 PRINT ' > TRUNCATE TABLE [' + @TableToTruncate + ']' IF @Debug = 1 PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']' ELSE EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']') IF @Verbose = 1 PRINT '5. Re-creating Foreign Keys...' -- CREATE FOREING KEYS SET @i = 1 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) BEGIN SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i) SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i) IF @Debug = 1 PRINT @Statement ELSE EXEC(@Statement) SET @i = @i + 1 IF @Verbose = 1 PRINT ' > Re-creating [' + @ConstraintName + ']' END IF @Verbose = 1 PRINT '6. Process Completed' END

Comments

Post a Comment

Popular posts from this blog

SOPA and the NDAA

-
During the Internet blackout today against SOPA, I was complaining on VoiceOfTheDBA that I wish we could get a blackout on the recent "indefinite detention of US citizens" fiasco. I said: Unfortunately (as you know) we have bigger issues. I’d love to see a blackout for basic liberty:   http://www.washingtonpost.com/opinions/is-the-united-states-still-the-land-of-the-free/2012/01/04/gIQAvcD1wP_story.html   http://www.huffingtonpost.com/2011/11/29/senate-votes-to-let-military-detain-americans-indefinitely_n_1119473.html   http://abcnews.go.com/blogs/politics/2011/12/with-reservations-obama-signs-act-to-allow-detention-of-citizens/ But now I've read the actual final section of the law under question, and couldn't figure out what the big deal is: http://thomas.loc.gov/cgi-bin/cpquery/?&dbname=cp112&sid=cp112MdQpb&refer=&r_n=hr329p1.112&item=&&&sel=TOC_1073854& Upon further research, I see that mostly the NDAA refused to clarify our sta...
Read more

Hiding an ASPXGridView Delete button with HTMLRowCreated vs. CommandButtonInitialize

-
It's pretty typical in Administration forms to need to hide buttons on certain rows for various reasons. Particularly it is nice to hide the Delete button if in fact the row in question is being used in other places in the app. This is a nice feature to let the user know up front that the row in question is in use, rather than them trying to delete it and getting a foreign key related error after the fact. For years now in DevExpress ASPxGridView's I have been handling this in the HtmlRowCreated event. And I have been doing it wrong. Before: protected void gridGiftCodes_HtmlRowCreated ( object sender, DevExpress.Web.ASPxGridViewTableRowEventArgs e) { if (e.RowType == GridViewRowType.Data){ int commandColumnIndex = - 1 ; //Find the command column commandColumnIndex = e.Row.Cells.Count- 1 ; //assume it's the last visible colum //Get the command cell for this row GridViewTableCommandCell cell = e.Row.Cells[commandColumnIndex] as Gri...
Read more

Specify the Server Name in your OutputFileDirectory for Hallengren SQL Server Backups

-
I've been playing with SQL Agent tokens more, researching how Ola Hallengren uses them in his famous Maintenance Solution, and how you can use them. Particularly I was looking for a way to specify the "ServerName" in the Output File path. You can do this using a token instead of having to customize the MaintenanceSolution.sql script for each server. This is helpful when you're doing environment takeovers and trying to get consistent backups going across several servers. Here is an example of our solution, which is working well. You would modify line 29 of Ola's script:   DECLARE @OutputFileDirectory nvarchar ( max ) = '\\MyBackupServer\DBBACKUPS\ $(ESCAPE_SQUOTE(SRVR)) \OUTPUT' Then for each job step, Ola will setup the following output file. This can be seen in the "Advanced" properties of a particular Job Step. '\\MyBackupServer\DBBACKUPS\$(ESCAPE_SQUOTE(SRVR))\OUTPUT\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DAT...
Read more