July 26, 2009 at 8:45 pm
10.Hello Team, There is a confusion while retrieving rows in the table
PurchaseOrderHeader in adventureworks DB. I used command as follows : select top 10 * from PurchaseOrderHeader
Error: Invalid object name 'PurchaseOrderHeader'.
When I execute following command then I get positive result:
SELECT * FROM SYSOBJECTS
WHERE name LIKE '%PurchaseOrderHeader%'
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----- ------ ------ ----------- --------------- ----------- ----------- ----------------------- ------- ----------- ---------------- ---- -------- ------- -------- ----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ------
PurchaseOrderHeader 434100587 U 8 0 0 0 0 0 2009-06-27 10:40:42.323 0 0 0 U 1 3 0 2009-06-27 10:40:42.323 0 0 0 1211151360 0 0 0
Now here uid=8 determines who is the owner of this table. From SSMS I can find this is ‘Purchasing’. Now when I execute this query, I get intended result:
select top 10 * from purchasing.PurchaseOrderHeader
Question: How to find ‘Purchasing’ without using SSMS.
July 26, 2009 at 8:58 pm
query the sys.schema view
select name from sys.schemas where schema_id in(select uid from sysobjects where name = 'PurchaseOrderDetail')
July 27, 2009 at 10:01 am
In SQL Server 2005 and later you should be querying the sys.tables or sys.all_objects views. These views provide the schema_id and you can then join on the sys.schemas. view to find the schema name
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 27, 2009 at 10:11 am
luckysql.kinda (7/26/2009)
Now here uid=8 determines who is the owner of this table.
Not in SQL 2005 and above it does not. It shows what schema the table is in. In SQL 2005 and above, users and schemas are split.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply