Created
August 1, 2017 19:48
-
-
Save SergVro/aec2e42d5375a2a3aed25d008db1eb79 to your computer and use it in GitHub Desktop.
Removes all objects from a SQL database
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
/* Drop all non-system stored procs */ | |
DECLARE @name VARCHAR(128) | |
DECLARE @SQL VARCHAR(254) | |
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) | |
WHILE @name is not null | |
BEGIN | |
SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']' | |
EXEC (@SQL) | |
PRINT 'Dropped Procedure: ' + @name | |
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) | |
END | |
GO | |
/* Drop all views */ | |
DECLARE @name VARCHAR(128) | |
DECLARE @SQL VARCHAR(254) | |
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) | |
WHILE @name IS NOT NULL | |
BEGIN | |
SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']' | |
EXEC (@SQL) | |
PRINT 'Dropped View: ' + @name | |
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) | |
END | |
GO | |
/* Drop all functions */ | |
DECLARE @name VARCHAR(128) | |
DECLARE @SQL VARCHAR(254) | |
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name]) | |
WHILE @name IS NOT NULL | |
BEGIN | |
SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']' | |
EXEC (@SQL) | |
PRINT 'Dropped Function: ' + @name | |
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) | |
END | |
GO | |
/* Drop all Foreign Key constraints */ | |
DECLARE @name VARCHAR(128) | |
DECLARE @constraint VARCHAR(254) | |
DECLARE @SQL VARCHAR(254) | |
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) | |
WHILE @name is not null | |
BEGIN | |
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) | |
WHILE @constraint IS NOT NULL | |
BEGIN | |
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']' | |
EXEC (@SQL) | |
PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name | |
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) | |
END | |
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) | |
END | |
GO | |
/* Drop all Primary Key constraints */ | |
DECLARE @name VARCHAR(128) | |
DECLARE @constraint VARCHAR(254) | |
DECLARE @SQL VARCHAR(254) | |
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) | |
WHILE @name IS NOT NULL | |
BEGIN | |
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) | |
WHILE @constraint is not null | |
BEGIN | |
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']' | |
EXEC (@SQL) | |
PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name | |
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) | |
END | |
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) | |
END | |
GO | |
/* Drop all tables */ | |
DECLARE @name VARCHAR(128) | |
DECLARE @SQL VARCHAR(254) | |
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name]) | |
WHILE @name IS NOT NULL | |
BEGIN | |
SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']' | |
EXEC (@SQL) | |
PRINT 'Dropped Table: ' + @name | |
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) | |
END | |
GO | |
/* Drop all types */ | |
DECLARE @name VARCHAR(128) | |
DECLARE @SQL VARCHAR(254) | |
SELECT @name = (SELECT TOP 1 RTRIM(name) FROM sys.types WHERE is_user_defined = 1) | |
WHILE @name IS NOT NULL | |
BEGIN | |
SELECT @SQL = 'DROP TYPE ' + RTRIM(@name) | |
EXEC (@SQL) | |
PRINT 'Dropped Type: ' + @name | |
SELECT @name = (SELECT TOP 1 RTRIM(name) FROM sys.types WHERE is_user_defined = 1) | |
END | |
GO | |
/* Drop all extra schemas */ | |
DECLARE @name VARCHAR(128) | |
DECLARE @SQL VARCHAR(254) | |
SELECT @name = (SELECT TOP 1 RTRIM(name) FROM sys.schemas WHERE [name] Like 'aspnet_%') | |
WHILE @name IS NOT NULL | |
BEGIN | |
SELECT @SQL = 'DROP SCHEMA ' + RTRIM(@name) | |
EXEC (@SQL) | |
PRINT 'Dropped Schema: ' + @name | |
SELECT @name = (SELECT TOP 1 RTRIM(name) FROM sys.schemas WHERE [name] Like 'aspnet_%') | |
END | |
GO | |
/* Drop all extra roles */ | |
DECLARE @name VARCHAR(128) | |
DECLARE @SQL VARCHAR(254) | |
DECLARE @cmd AS VARCHAR(MAX) = N''; | |
SELECT @name = (SELECT TOP 1 RTRIM(name) FROM sysusers WHERE issqlrole = 1 AND [name] Like 'aspnet_%') | |
WHILE @name IS NOT NULL | |
BEGIN | |
SELECT @cmd = @cmd + ' | |
ALTER ROLE '+@name+' DROP MEMBER '+members.[name]+';' | |
FROM sys.database_role_members AS rolemembers | |
JOIN sys.database_principals AS roles | |
ON roles.[principal_id] = rolemembers.[role_principal_id] | |
JOIN sys.database_principals AS members | |
ON members.[principal_id] = rolemembers.[member_principal_id] | |
WHERE roles.[name]=@name | |
EXEC(@cmd); | |
SELECT @SQL = 'DROP ROLE ' + RTRIM(@name) | |
EXEC (@SQL) | |
PRINT 'Dropped Role: ' + @name | |
SELECT @name = (SELECT TOP 1 RTRIM(name) FROM sysusers WHERE issqlrole = 1 AND [name] Like 'aspnet_%') | |
END | |
GO | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment