Last active
February 21, 2017 07:56
-
-
Save bhasto/4534514 to your computer and use it in GitHub Desktop.
SQL Server - Drop all tables in given schema using Powershell
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
sqlps -Command { | |
Invoke-Sqlcmd -ServerInstance "SERVER[,PORT]" -Database DB_NAME -Username sa -Password sa -Variable "SchemaName='dbo'" -InputFile "clean-db.sql" | |
} |
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 @statement VARCHAR(MAX) | |
-- Generate drop statement for all foreign key constraints | |
DECLARE cur cursor FOR | |
SELECT 'ALTER TABLE "' + TABLE_SCHEMA + '"."' + TABLE_NAME + '" DROP CONSTRAINT ' + CONSTRAINT_NAME | |
FROM information_schema.table_constraints | |
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_SCHEMA = $(SchemaName) | |
-- Iterate over drop statement and execute them | |
OPEN cur | |
FETCH NEXT FROM cur INTO @statement | |
WHILE (@@fetch_status = 0) BEGIN | |
PRINT @statement | |
EXEC (@statement) | |
FETCH NEXT FROM cur INTO @statement | |
END | |
CLOSE cur | |
DEALLOCATE cur | |
-- Generate drop statement for all tables in schema | |
DECLARE cur cursor FOR | |
SELECT 'DROP ' + CASE | |
WHEN o.xtype = 'U' THEN 'TABLE' | |
WHEN o.xtype = 'V' THEN 'VIEW' | |
WHEN o.xtype = 'P' THEN 'PROCEDURE' | |
WHEN o.xtype = 'FN' THEN 'FUNCTION' | |
END + ' "' + s.name + '"."' + o.name + '"' | |
FROM | |
sys.sysobjects AS o | |
JOIN sys.schemas AS s ON o.uid = s.schema_id | |
WHERE | |
s.name = $(SchemaName) AND | |
o.xtype IN ('U','V','P','FN') | |
-- Iterate over drop statement and execute them | |
OPEN cur | |
FETCH NEXT FROM cur INTO @statement | |
WHILE (@@fetch_status = 0) BEGIN | |
PRINT @statement | |
EXEC (@statement) | |
FETCH NEXT FROM cur INTO @statement | |
END | |
CLOSE cur | |
DEALLOCATE cur |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment