Today I ran into a problem where all of my SQL Server tables used a different schema than dbo and the application couldn’t understand the different schema. Using the ALTER SCHEMA statement you can chance the schema of a table, for example, the following statement will change the schema from compmsauser to dbo.
ALTER SCHEMA dbo TRANSFER compmsauser.tablename
This works perfectly unless you have to change hundreds of table schemas. The following query will create the T-SQL needed to change every table: (change the WHERE clause to the schema you need to replace)
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name FROM sys.Objects o INNER JOIN sys.Schemas s on o.schema_id = s.schema_id WHERE s.Name = 'compmsauser' And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')