Created
October 9, 2019 07:36
-
-
Save mykeels/17ef8f3038c5ede2bb3f73ffd9c90c8f to your computer and use it in GitHub Desktop.
Delete all schema in an SQL Server db, without dropping it.
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
DECLARE @sql nvarchar(MAX) | |
SET @sql = N'' | |
SELECT @sql = @sql + N'ALTER TABLE ' + QUOTENAME(KCU1.TABLE_SCHEMA) | |
+ N'.' + QUOTENAME(KCU1.TABLE_NAME) | |
+ N' DROP CONSTRAINT ' -- + QUOTENAME(rc.CONSTRAINT_SCHEMA) + N'.' -- not in MS-SQL | |
+ QUOTENAME(rc.CONSTRAINT_NAME) + N'; ' + CHAR(13) + CHAR(10) | |
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC | |
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 | |
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG | |
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA | |
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME | |
-- PRINT @sql | |
EXECUTE(@sql) | |
SET @sql = N'' | |
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
I find this useful for DBs created on Azure, where dropping the DB is essentially killing the Service that powers it.