Problem with multipart sql identifiers

Question

I've successfully migrated a website running a SQL Server 2005 db to a new server running SQL Server 2008 R2 and everything seems to work fine except 1 thing.

2 different schemas where used when creating the tables (I have no idea why). So you have to call the tables using a multipart identifier like schemaname.tablename except in the websites sql code they call everything using either just tablename or schemaname.tablename which worked before but not now.

The website itself is a huge mess and would be extremely difficult to find and modify all the sql queries.

Does anyone know how I can change it so it allows queries with just tablename again?

Answer 1

You can use ALTER SCHEMA to move securables between schemas in the same database.

This script will transfer tables, stored procedures and views in 'myschema' to 'dbo' schema. Modify to suit your needs and run this TSQL script:

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 = 'myschema'
AND (o.Type = 'U' OR o.Type = 'P' OR o.Type = 'V')

and then copy the output to another query window and run. (Be careful, check everything is correct before moving)

Answer 2

One way to accomplish what you want is to have the different programs log in using different identities. You can then use ALTER USER or the SSMS UI to set the default schema of each user. The default schema specifies which schema is used when a user doesn't use a prefix.

Another thing to do, if you want to consolidate the schemas, would be to define synonyms for each object in the schema you want to get rid of, so you can eventually consolidate stuff down to one clean schema over time.