Solved

sysobjects vs all_objects

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

856 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