Solved

sysobjects vs all_objects

Posted on 2010-08-30
4
1,014 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
  • 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now