In older versions of Microsoft SQL Server (and Sybase?), a separate database schema was created for each database user. This caused confusion and resulted in non-standard fully qualified object names like jsmith.tabname rather than dbo.tabname.
To correct this, you can assign the dbo default schema to each database user and migrate all objects into the dbo schema. Note that you must migrate all objects out of the schema prior to deleting it.
To find objects in other schemas, use a command like this:
USE [myDatabaseName] SELECT SCHEMA_NAME(schema_id) AS [schema] ,* FROM sys.objects WHERE SCHEMA_NAME(schema_id) <> 'dbo' AND SCHEMA_NAME(schema_id) <> 'sys'
To migrate objects into the dbo schema, use a command like this:
USE [myDatabaseName] ALTER SCHEMA dbo TRANSFER OldSchema.TableName;
Once the objects have been moved to the dbo schema, you can delete the old schema if desired using “DROP SCHEMA [OldSchemaName]” command. See the following articles for reference and further reading.
- How to find all objects that belong to the specific schema?
- How do I move a table into a schema in T-SQL
- … how do I change the “schema” of a table … ?