Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Disable all constraints for database
- EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
- DECLARE @catalog nvarchar(250);
- DECLARE @schema nvarchar(250);
- DECLARE @tbl nvarchar(250);
- DECLARE i CURSOR LOCAL FAST_FORWARD FOR select
- TABLE_CATALOG,
- TABLE_SCHEMA,
- TABLE_NAME
- from INFORMATION_SCHEMA.TABLES
- where
- TABLE_TYPE = 'BASE TABLE'
- AND TABLE_NAME != 'sysdiagrams'
- AND TABLE_NAME != '__RefactorLog'
- OPEN i;
- FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
- WHILE @@FETCH_STATUS = 0
- BEGIN
- DECLARE @sql NVARCHAR(MAX) = N'DELETE FROM [' + @catalog + '].[' + @schema + '].[' + @tbl + '];'
- /* Make sure these are the commands you want to execute before executing */
- PRINT 'Executing statement: ' + @sql
- EXECUTE sp_executesql @sql
- -- Reset identity counter if one exists
- IF ((SELECT OBJECTPROPERTY( OBJECT_ID(@catalog + '.' + @schema + '.' + @tbl), 'TableHasIdentity')) = 1)
- BEGIN
- SET @sql = N'DBCC CHECKIDENT ([' + @catalog + '.' + @schema + '.' + @tbl + '], RESEED, 0)'
- PRINT 'Executing statement: ' + @sql
- EXECUTE sp_executesql @sql
- END
- FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
- END
- CLOSE i;
- DEALLOCATE i;
- -- Enable all constraints for database
- EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement