Solved

sysobjects vs all_objects

Posted on 2010-08-30
4
1,116 Views
Last Modified: 2012-05-10
select * from sys.sysobjects where user_name(uid) is null and type = 'U'
brings back 54 records
select * from sys.all_objects where principal_id is null  and type = 'U'
brings 396 records.

Both should bring back the tables in the database; why the diff and what is the difference?
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
Scott Madeira earned 500 total points
ID: 33558563
sysobjects uid gives you the uid of the owner of the object.

from MSDN:
uid is the Schema ID of the owner of the object. For databases upgraded from an earlier version of SQL Server, the schema ID is equal to the user ID of the owner.


principal_id gives you the owner of the object if the object is not owned by the schema which implies that is all of the objects are owned by the schema then this field would be null for all objects.

from MSDN: principal_id is the ID of the individual owner if different from the schema owner. By  default, schema-contained objects are owned by the schema owner.  However, another owner can be specified by using the ALTER AUTHORIZATION  statement to change ownership.

Hope that helps.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33559759
>>if all of the objects are owned by the schema then this field would be null for all objects

the only alternative for being owned by the schema is being owned by dbo, right?

I have
select * from sys.all_objects where principal_id is not null
return 0 rows back. Does that mean all objects are not owned by the schema?


0
 
LVL 14

Assisted Solution

by:Scott Madeira
Scott Madeira earned 500 total points
ID: 33560334
>> select * from sys.all_objects where principal_id is not null
return 0 rows back. Does that mean all objects are not owned by the schema?

That would mean all objects are owned by the schema.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33560711
OK. then when only will it have a valid principal_id: even for dbo it is NULL

create database test
go
use test
create table test(test int)
select * from sys.all_objects where type = 'u' and principal_id is not null
use master
drop database test
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question