Click here to Skip to main content
14,362,953 members
   

Database

 
AnswerRe: DATABASE Maintainance Pin
Victor Nijegorodov6-Aug-18 9:01
memberVictor Nijegorodov6-Aug-18 9:01 
AnswerRe: DATABASE Maintainance Pin
Eddy Vluggen6-Aug-18 9:15
mveEddy Vluggen6-Aug-18 9:15 
GeneralRe: DATABASE Maintainance Pin
CHill606-Aug-18 23:01
protectorCHill606-Aug-18 23:01 
Questionrecovery method for hard drive Pin
Member 139376004-Aug-18 5:08
memberMember 139376004-Aug-18 5:08 
AnswerRe: recovery method for hard drive Pin
Victor Nijegorodov4-Aug-18 21:34
memberVictor Nijegorodov4-Aug-18 21:34 
QuestionNeed to drop a schema in which there are some objects exist Pin
indian1431-Aug-18 7:28
memberindian1431-Aug-18 7:28 
AnswerRe: Need to drop a schema in which there are some objects exist Pin
Mycroft Holmes1-Aug-18 16:03
memberMycroft Holmes1-Aug-18 16:03 
AnswerRe: Need to drop a schema in which there are some objects exist Pin
CHill602-Aug-18 3:26
protectorCHill602-Aug-18 3:26 
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

QuestionRe: Need to drop a schema in which there are some objects exist Pin
CHill606-Aug-18 2:40
protectorCHill606-Aug-18 2:40 
GeneralLegacy software: can't load MSJTER35.DLL Pin
Keith Sheppard228-Jul-18 0:26
memberKeith Sheppard228-Jul-18 0:26 
GeneralRe: Legacy software: can't load MSJTER35.DLL Pin
Victor Nijegorodov28-Jul-18 0:45
memberVictor Nijegorodov28-Jul-18 0:45 
GeneralRe: Legacy software: can't load MSJTER35.DLL Pin
Keith Sheppard22-Aug-18 2:23
memberKeith Sheppard22-Aug-18 2:23 
Questioninner join results Pin
Member 1386716325-Jul-18 19:12
memberMember 1386716325-Jul-18 19:12 
AnswerRe: inner join results Pin
Richard Deeming26-Jul-18 2:50
mveRichard Deeming26-Jul-18 2:50 
AnswerRe: inner join results Pin
Victor Nijegorodov26-Jul-18 2:54
memberVictor Nijegorodov26-Jul-18 2:54 
QuestionCreation of documentation automatically when creating or updating a stored procedure Pin
indian14323-Jul-18 14:49
memberindian14323-Jul-18 14:49 
AnswerRe: Creation of documentation automatically when creating or updating a stored procedure Pin
Victor Nijegorodov24-Jul-18 11:12
memberVictor Nijegorodov24-Jul-18 11:12 
AnswerRe: Creation of documentation automatically when creating or updating a stored procedure Pin
jschell28-Jul-18 8:13
memberjschell28-Jul-18 8:13 
QuestionJob runs 3 times longer Pin
VK1920-Jul-18 8:12
memberVK1920-Jul-18 8:12 
AnswerRe: Job runs 3 times longer Pin
Victor Nijegorodov20-Jul-18 9:01
memberVictor Nijegorodov20-Jul-18 9:01 
AnswerRe: Job runs 3 times longer Pin
Eddy Vluggen28-Jul-18 1:16
mveEddy Vluggen28-Jul-18 1:16 
GeneralRe: Job runs 3 times longer Pin
jschell28-Jul-18 8:14
memberjschell28-Jul-18 8:14 
GeneralRe: Job runs 3 times longer Pin
Eddy Vluggen28-Jul-18 11:23
mveEddy Vluggen28-Jul-18 11:23 
GeneralRe: Job runs 3 times longer Pin
digimanus14-Aug-18 4:01
memberdigimanus14-Aug-18 4:01 
GeneralRe: Job runs 3 times longer Pin
Eddy Vluggen14-Aug-18 4:08
mveEddy Vluggen14-Aug-18 4:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.