A rookie developer reached out to me requesting help to move a couple of tables to a new schema. Thinking the developer did not have permissions to the one of the schemas, I agreed to help. However, when I ran the “Alter Schema” statement, I received the following error:
Msg 15151, Level 16, State 1, Line 1 Cannot alter the schema 'HR', because it does not exist or you do not have permission.
After a little digging, I found that the reason the developer could not alter the schema for the tables, was because the schema did not exist in this database. The tables moved to the new schema easily after I ran the following SQL to create the schema:
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = ' <new schema name > ')) BEGIN EXEC ('CREATE SCHEMA [ <new schema name > ] AUTHORIZATION [dbo]') END ALTER SCHEMA [<new schema name >] TRANSFER [dbo].[ <table> ]; GO