Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* TRUNCATE ALL TABLES IN A DATABASE */
- DECLARE @dropAndCreateConstraintsTable TABLE
- (
- DropStmt VARCHAR(MAX)
- ,CreateStmt VARCHAR(MAX)
- )
- /* Gather information to drop and then recreate the current foreign key constraints */
- INSERT @dropAndCreateConstraintsTable
- SELECT DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
- + '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT ['
- + ForeignKeys.ForeignKeyName + ']; '
- ,CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
- + '].[' + ForeignKeys.ForeignTableName
- + '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName
- + '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn
- + ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id)
- + '].[' + sys.objects.[name] + ']([' + sys.COLUMNS.[name]
- + ']); '
- FROM sys.objects
- INNER JOIN sys.COLUMNS
- ON ( sys.COLUMNS.[object_id] = sys.objects.[object_id] )
- INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName
- ,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema
- ,sys.objects.[name] AS ForeignTableName
- ,sys.COLUMNS.[name] AS ForeignTableColumn
- ,sys.foreign_keys.referenced_object_id AS referenced_object_id
- ,sys.foreign_key_columns.referenced_column_id AS referenced_column_id
- FROM sys.foreign_keys
- INNER JOIN sys.foreign_key_columns
- ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] )
- INNER JOIN sys.objects
- ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id )
- INNER JOIN sys.COLUMNS
- ON ( sys.COLUMNS.[object_id] = sys.objects.[object_id] )
- AND ( sys.COLUMNS.column_id = sys.foreign_key_columns.parent_column_id )
- ) ForeignKeys
- ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
- AND ( ForeignKeys.referenced_column_id = sys.COLUMNS.column_id )
- WHERE ( sys.objects.[TYPE] = 'U' )
- AND ( sys.objects.[name] NOT IN ( 'sysdiagrams' ) )
- /* SELECT * FROM @dropAndCreateConstraintsTable AS DACCT --Test statement*/
- DECLARE @DropStatement NVARCHAR(MAX)
- DECLARE @RecreateStatement NVARCHAR(MAX)
- /* Drop Constraints */
- DECLARE Cur1 CURSOR READ_ONLY
- FOR
- SELECT DropStmt
- FROM @dropAndCreateConstraintsTable
- OPEN Cur1
- FETCH NEXT FROM Cur1 INTO @DropStatement
- WHILE @@FETCH_STATUS = 0
- BEGIN
- PRINT 'Executing ' + @DropStatement
- EXECUTE sp_executesql @DropStatement
- FETCH NEXT FROM Cur1 INTO @DropStatement
- END
- CLOSE Cur1
- DEALLOCATE Cur1
- /* Truncate all tables in the database in the dbo schema */
- DECLARE @DeleteTableStatement NVARCHAR(MAX)
- DECLARE Cur2 CURSOR READ_ONLY
- FOR
- SELECT 'TRUNCATE TABLE [dbo].[' + TABLE_NAME + ']'
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = 'dbo'
- AND TABLE_TYPE = 'BASE TABLE'
- /* Change your schema appropriately if you don't want to use dbo */
- OPEN Cur2
- FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
- WHILE @@FETCH_STATUS = 0
- BEGIN
- PRINT 'Executing ' + @DeleteTableStatement
- EXECUTE sp_executesql @DeleteTableStatement
- FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
- END
- CLOSE Cur2
- DEALLOCATE Cur2
- /* Recreate foreign key constraints */
- DECLARE Cur3 CURSOR READ_ONLY
- FOR
- SELECT CreateStmt
- FROM @dropAndCreateConstraintsTable
- OPEN Cur3
- FETCH NEXT FROM Cur3 INTO @RecreateStatement
- WHILE @@FETCH_STATUS = 0
- BEGIN
- PRINT 'Executing ' + @RecreateStatement
- EXECUTE sp_executesql @RecreateStatement
- FETCH NEXT FROM Cur3 INTO @RecreateStatement
- END
- CLOSE Cur3
- DEALLOCATE Cur3
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement