Wednesday, October 3, 2012

Convert all tables in a database to a specific schema

Create schema

CREATE SCHEMA sampleSchema
GO


Create a T-SQL statement to transfer all tables to created schema

DECLARE @sql NVARCHAR(MAX) ;
DECLARE @LINEBREAK AS VARCHAR(2)
SET @LINEBREAK = CHAR(13) + CHAR(10)
        
SELECT  @sql = COALESCE(@sql'; ' + @LINEBREAK , '') + 'ALTER SCHEMA [sampleSchema] TRANSFER '
        + QUOTENAME([TABLE_SCHEMA]) + '.' + QUOTENAME([TABLE_NAME])
    
FROM    INFORMATION_SCHEMA.TABLES

PRINT @sql
EXECUTE sp_executesql @sql

1 comment: