Scenario:You are working for Mortgage Company as SQL Server developer. You got this requirement to prepare SQL script to drop schema. You use
Drop Schema SchemeName
statement in development SQL Server Instance and get below error.
Cannot drop the schema '', because it does not exist or you do not have permission.
What would be your next steps?
Declare @SourceSchema VARCHAR(100) Declare @DestinationSchema VARCHAR(100) SET @SourceSchema='TB' SET @DestinationSchema='dbo' Select 'Alter Schema ['+@DestinationSchema+'] Transfer '+@SourceSchema+'.['+name+']'
from sys.objects where schema_name(schema_id)=@SourceSchema
Copy the generated scripts and then run in a database. Once done, Now you are good to drop the Schema by using Drop Schema SchemaName Statement.