CODEWITHSUNDEEP
sql-servert-sql
ʃʈɑɲ
ʃʈɑɲ

Reputation: 2684

Drop a schema using variable name

I have the following:

DECLARE @SchemaName NVARCHAR(MAX)
SET @SchemaName = 'MySchema'

DROP SCHEMA MySchema

How should I go to use the variable? I tried DROP SCHEMA OBJECT_ID(@SchemaName)

Maybe DELETE FROM SYS.SCHEMAS WHERE NAME = @SchemaName equals a DROP?

Upvotes: 1

Views: 1234

Answers (2)

RoKa
RoKa

Reputation: 160

Try something like this: 

DECLARE @sql nvarchar(max), @SchemaName NVARCHAR(MAX)
SET @SchemaName = 'MySchema'

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

---print @sql
exec sp_executesql(@sql)

Upvotes: 4

Pரதீப்
Pரதீப்

Reputation: 93734

Try using Dynamic sql

DECLARE @SchemaName NVARCHAR(MAX)
SET @SchemaName = 'test'

DECLARE @sql NVARCHAR(128)= ''

SET @sql = 'DROP SCHEMA ' + quotename(@SchemaName) + ''

--print @sql
EXEC (@sql)

Also i don't think you can delete a record from SYS.SCHEMAS view

Upvotes: 3

Related Questions