Database

Quote:

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

-- 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

-- 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

Create proc ....
@InputID int
as
Declare LocalID int
Set @LocalID = @InputID

select *
from Employee
where DepartmentID =(
select top 1 DepartmentID from(
select DepartmentID , count(*) C
from Employee
group by DepartmentID) x order by C desc)