This proc comes in handy when you have to purge all the
objects in your database, so you can then run a creation script. It will tell you how many objects it dropped.
Use Admin
GO
IF
Object_ID('dbo.DropObjectsInSchema')
Is Not Null
DROP PROC dbo.DropObjectsInSchema
go
CREATE PROC dbo.DropObjectsInSchema
@SchemaName varchar(50),
@ObjectType varchar(50),
@Debug bit = 0
AS
/* DATE AUTHOR REMARKS
11/11/13 PPaiva Initial
creation.
DESCRIPTION
Drops
all objects of type @ObjectType in a given schema.
USAGE
Exec
DropObjectsInSchema 'dbo', 'FK', 1
Exec
DropObjectsInSchema 'dbo', 'Table', 1
Exec
DropObjectsInSchema 'dbo', 'View', 1
Exec
DropObjectsInSchema 'dbo', 'Proc', 1
Exec
DropObjectsInSchema 'dbo', 'Function', 1
DropObjectsInSchema
'dbo', 'FK', 1
DropObjectsInSchema
'dbo', 'FK'
DropObjectsInSchema
'dbo', 'Table', 1
DropObjectsInSchema
'dbo', 'Table'
DropObjectsInSchema
'dbo', 'View', 1
DropObjectsInSchema
'dbo', 'View'
DropObjectsInSchema
'dbo', 'Proc', 1
DropObjectsInSchema
'dbo', 'Proc'
DropObjectsInSchema
'dbo', 'Function', 1
DropObjectsInSchema
'dbo', 'Function'
DEBUG
SELECT
*
FROM
sys.schemas
WHERE
Name Not Like 'db_%'
SELECT
type_desc, Count(*) Qty
FROM
sys.objects
GROUP
BY type_desc
ORDER
BY 1
SELECT
*
FROM
sys.objects
WHERE
type_desc <> 'system_table'
SELECT
*
FROM
vwDictCol
*/
SET NOCOUNT ON
DECLARE @Name varchar(100),
@MaxName varchar(100),
@Sql varchar(1000),
@Num int,
@Msg varchar(100)
IF
@ObjectType Not
In ( 'FK', 'Table', 'View', 'Proc', 'Function')
BEGIN
SET @Msg = '@ObjectType must be one of
these: FK, Table, View, Proc, Function'
Print @Msg
RaisError (@Msg, 16, 1)
RETURN
END
CREATE TABLE #t(
ID int NOT NULL IDENTITY(1, 1),
Name sysname,
Sql varchar(2000)
)
IF
@ObjectType = 'FK'
INSERT INTO #t (Name, Sql)
SELECT f.Name,
'ALTER TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + object_Name(f.parent_object_id) + ']'
+ ' DROP CONSTRAINT ['
+ f.name + ']' Sql
FROM sys.foreign_keys f
JOIN sys.objects o
ON o.object_id = f.parent_object_id
WHERE SCHEMA_NAME(o.schema_id) = @SchemaName
ORDER BY o.Name
ELSE IF @ObjectType
= 'Table'
INSERT INTO #t (Name, Sql)
SELECT o.Name,
'DROP TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql
FROM sys.objects o
WHERE type_desc = 'user_table'
AND SCHEMA_NAME(o.schema_id) = @SchemaName
AND o.Name Not In ('sysdiagrams', 'Flag', 'logEvent', 'History')
AND o.Name Not Like 'rsa%'
ORDER BY o.Name
ELSE IF @ObjectType
= 'View'
INSERT INTO #t (Name, Sql)
SELECT o.Name,
'DROP VIEW [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql
FROM sys.objects o
WHERE type_desc = 'View'
AND SCHEMA_NAME(o.schema_id) = @SchemaName
AND o.Name Not In ('vwDictCol', 'vwIndexRaw', 'vwRowCount', 'vwTrigger')
ORDER BY o.Name
ELSE IF @ObjectType
= 'Proc'
INSERT INTO #t (Name, Sql)
SELECT o.Name,
'DROP PROC [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql
FROM sys.objects o
WHERE type_desc Like '%procedure%'
AND SCHEMA_NAME(o.schema_id) = @SchemaName
AND o.Name Not Like 'sp%diagram%'
AND o.name Not In ( 'DropObjectsInSchema', 'WriteLogEvent')
ORDER BY o.Name
ELSE IF @ObjectType
= 'Function'
INSERT INTO #t (Name, Sql)
SELECT o.Name,
'DROP FUNCTION [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql
FROM sys.objects o
WHERE type_desc Like '%function%'
AND SCHEMA_NAME(o.schema_id) = @SchemaName
AND o.Name Not In ('fn_diagramobjects', 'udfFormatInteger', 'udfGetColumnList', 'udfGetEntityRoleId', 'udfFormatTimeWithColons')
ORDER BY o.Name
SELECT @MaxName
= Max(Name),
@Name = '',
@Num = Count(*)
FROM #t
SET @Msg =
@ObjectType + 's found in schema ' +
@SchemaName + ': ' + Convert(varchar, @Num)
Print ''
Print @Msg
WHILE @Name <
@MaxName
BEGIN
-- Get next Name
SELECT @Name = Min(Name)
FROM #t
WHERE Name > @Name
-- Get other attibutes
SELECT @Sql = Sql
FROM #t
WHERE Name = @Name
IF @Debug = 1
Print @Sql
ELSE
Exec (@Sql)
END
