PDA

View Full Version : MSSQL Server - Error: invalid object name



JonC
September 11th, 2009, 01:41 PM
When performing a query of a MS SQL Server database you receive the following error "invalid object name". This error is generated when a table is pre-fixed with the schema other than dbo (example: myuser.table).

Querying "select * from myuser.table" would allow you to query the table but is not convenient.

Instead the table should be pre-fixed with 'dbo'. to do this you will need to ALTER each of the tables with this statement:



ALTER SCHEMA dbo TRANSFER myuser.table


To generate all the needed SQL to change ALL tables:



SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name
FROM sys.tables p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name = 'EXISTING_OWNER_USERNAME'

jakeh
January 20th, 2010, 05:13 PM
If you need to update SQL Server Objects like stored procedures, please change 'sys.tables' in the above query to 'sys.objects'.

JonC
March 8th, 2010, 10:03 AM
For SQL 2000 change the owner on a table:
1. Change MyDB to your database name.
2. Change MyUser to the current owner username.
3. Change MyTableName to the table needing modified.



use MyDB

EXEC sp_changeobjectowner 'MyUser.MyTableName', 'dbo'