This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing one T-SQL script to change the schema name of all the tables in a SQL Server Database.
When you require to alter your database schema name and you have a large number of tables, you can use this kind of script to alter the schema name.
First, you need to create one new schema and you can replace [New_Schema_Name] by your newly created schema name. You can also add a table filter in the cursor main query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE [DB_NAME] GO DECLARE @TABNAME VARCHAR(255) DECLARE @SQLCMD VARCHAR(500) DECLARE CHANGESCHEMA CURSOR FOR SELECT TABNAME FROM (SELECT '['+B.NAME+'].['+A.NAME+']' TABNAME FROM SYS.TABLES A INNER JOIN SYS.SCHEMAS B ON A.SCHEMA_ID= B.SCHEMA_ID) T OPEN CHANGESCHEMA FETCH NEXT FROM CHANGESCHEMA INTO @TABNAME WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLCMD= 'ALTER SCHEMA [New_Schema_Name] TRANSFER '+@TABNAME PRINT @SQLCMD EXEC(@SQLCMD) FETCH NEXT FROM CHANGESCHEMA INTO @TABNAME END CLOSE CHANGESCHEMA DEALLOCATE CHANGESCHEMA GO |
