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