Drop a sql schema with its referencing objects

As you may know if you want to drop a schema in sql you have to drop all its objects by hands first (tables, views, procedures, functions). Of course this restriction is essential to avoid loosing of data.

But if you are sure that you want to drop all of objects you can use this procedure

CREATE PROCEDURE dbo.DropSchemaWithObjects

@schema NVARCHAR(MAX)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”

select
@SQL = @SQL + CHAR(13) + CHAR(10) +
‘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 = @schema and
o.xtype in (‘U’,’V’,’P’,’FN’)

SET @sql = @sql + CHAR(13) + CHAR(10) + ‘ DROP SCHEMA ‘ + @schema

EXECUTE sp_executesql @sql
END

Advertisements
This entry was posted in Microsoft SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s