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?

回答1:

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)

回答2:

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