Need to drop a schema in which there are some objects exist

indian143

Hi, I need to drop a schema in which there are some tables, is there anyway? when I searched, the links are showing dynamic sql to generate drop table text, I want to check before dropping with if exists condition, if I am dropping them, can somebody give me any link or advice or something? I am also researching about it as I am in little urgency I am writing here if I can get some help faster, any help would be greatly helpful - thanks in advance. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Mycroft Holmes

I'm almost certain you can't have a table with no schema so dropping a schema with existing tables should not be possible. I have no idea if it is because it seems so obvious that you should not do this, I have never tried it. You can move table to another schema and then drop the schema, caveat the transfer does not change the views and procedures referencing the dropped schema. Did this recently, thankfully it was a small database.

Never underestimate the power of human stupidity RAH

CHill60

I'm not entirely sure but I think you are trying to a) determine if there are any tables in a schema b) if there are tables then drop them c) then drop the schema I'm guessing that you are currently getting the error

Quote:

Cannot drop schema 'InsertYourSchemaName' because it is being referenced by object

The following code steps through all the tables (if any) in a named schema (I've included views as well). It will drop the table (or view) then drop the schema at the end

-- The schema we are trying to drop
declare @schemaName varchar(255) = 'Test'
-- a temporary table for the list of things in the schema
declare @thingsToDrop table (TABLE_NAME varchar(255), TABLE_TYPE varchar(255))
insert into @thingsToDrop (TABLE_NAME, TABLE_TYPE)
SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @schemaName

-- Declare the variables for the cursor
DECLARE @name varchar(255)
DECLARE @type varchar(30)

-- Set up the cursor
IF CURSOR_STATUS('global','myCursor')>=-1 DEALLOCATE tb_cursor
DECLARE tb_cursor CURSOR FOR
SELECT TABLE_NAME, TABLE_TYPE FROM @thingsToDrop

OPEN tb_cursor
FETCH NEXT FROM tb_cursor INTO @name, @type

-- step through the list of things to dispose of
DECLARE @sql nvarchar(max)
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @thing varchar(10)
SET @thing = (SELECT CASE WHEN @type = 'BASE TABLE' THEN 'table'
WHEN @type = 'VIEW' THEN 'view' -- etc as required
END)
SET @sql = CONCAT('DROP ',@thing, ' ',@schemaName, '.', @name)
--If you have SQL Server 2017 you could use CONCAT_WS instead

EXEC sp\_executesql @sql

FETCH NEXT FROM tb\_cursor INTO @name, @type

END

CLOSE tb_cursor
DEALLOCATE tb_cursor

set @sql = 'DROP SCHEMA ' + quotename(@SchemaName)
exec sp_executesql @sql

I tested it with these examples

-- test 1
;CREATE SCHEMA Test;
GO
create table Test.test(id int identity(1,1))
create table Test.test1(id int identity(1,1))
GO

;CREATE VIEW Test.ViewTest AS
SELECT * FROM Test.test UNION ALL SELECT * FROM Test.test1
GO

-- test 2
;CREATE SCHEMA Test;
GO
;CREATE VIEW Test.ViewTest AS
INFORMATION_SCHEMA.TABLES
GO

-- test 3
;CREATE SCHEMA Test;
GO
create table Test.test(id int identity(1,1))
create table Test.test1(id int identity(1,1))
GO

-- test 4
;CREATE SCHEMA Test;
GO

CHill60

Is this still a problem?