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?
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)
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
EXEC (@sql) rather than EXEC @sql, also, it is a good idea to use QUOTENAME() rather than manually concatenating brackets, as QUOTENAME() will escape characters properly. Consider the following schema - CREATE SCHEMA "Stupid[]Name"; -- Using quotename would escape this correctly to give: [Stupid[]]Name], whereas just concatenating brackets will give [Stupid[]Name], resulting in a syntax error. While I admit this is an abhorrent object name, but sometimes people need to cater for this.
