Cursor Changing Schema Name in SQL Server

Hello everyone,

In this article, I will provide information on how to write a cursor that changes the schema name in SQL Server.

While researching in SQL Server I was wondering how to change all schema names in one go. You can see the sample code for this below.

Just do not start this process without creating the schema you want to modify before doing this, or you will get an error. I will explain the operations here on the Northwind database. You can also work on this database to learn. Make sure you fully understand the process before attempting it on a live database.

DECLARE @currentSchemaName NVARCHAR(200),
        @tableName NVARCHAR(200);
DECLARE tableCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_SCHEMA,
       TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY 1,
         2;
DECLARE @SQL NVARCHAR(400);
OPEN tableCursor;
FETCH NEXT FROM tableCursor
INTO @currentSchemaName,
     @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'ALTER SCHEMA YeniSema TRANSFER ' + @currentSchemaName + N'.[' + @tableName + N']';
    PRINT @SQL; --You can also see the SQL Query in this section.
    EXEC (@SQL);
    FETCH NEXT FROM tableCursor
    INTO @currentSchemaName,
         @tableName;
END;
CLOSE tableCursor;
DEALLOCATE tableCursor;

The part I call NewSema is the name of the schema I created. If you have created a schema before, you can write your schema name here.

Let’s look at the table names when you run the code.

As you can see, schema names from dbo have been changed to YeniSema.

Good luck to everyone in business and life.