Last active
July 9, 2020 17:30
-
-
Save zimonitrome/0583c40f32aff0896317f6152ac5d276 to your computer and use it in GitHub Desktop.
Used to purge a database. ERASE IT ALL.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE databaseName | |
/* ====== DROP ALL RELATIONS ====== */ | |
DECLARE @SQL VARCHAR(MAX)='' | |
SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13) | |
--SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME | |
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C | |
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK | |
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME | |
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK | |
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME | |
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU | |
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME | |
INNER JOIN ( | |
SELECT i1.TABLE_NAME, i2.COLUMN_NAME | |
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 | |
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 | |
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME | |
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' | |
) PT | |
ON PT.TABLE_NAME = PK.TABLE_NAME | |
EXEC (@SQL) | |
GO | |
/* ====== DROP ALL TABLES ====== */ | |
DECLARE @sql NVARCHAR(max)='' | |
SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; ' | |
FROM INFORMATION_SCHEMA.TABLES | |
WHERE TABLE_TYPE = 'BASE TABLE' | |
Exec Sp_executesql @sql |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment