Solved

sysobjects vs all_objects

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 98
Permissions on Database 11 37
How to use Full Text CONTAINS with Case in SQL 6 20
Need SQL that flips  value 5 10
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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 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

16 Experts available now in Live!

Get 1:1 Help Now