Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1187
  • Last Modified:

sysobjects vs all_objects

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
anushahanna
Asked:
anushahanna
  • 2
  • 2
2 Solutions
 
Scott MadeiraCommented:
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
 
anushahannaAuthor Commented:
>>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
 
Scott MadeiraCommented:
>> 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
 
anushahannaAuthor Commented:
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

Independent Software Vendors: 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!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now