CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008sql-view
Kevin
Kevin

Reputation: 205

SQL Server 2008 sys.objects equivalent to sysobjects.uid

Does anyone know how to get an equivalent of the SQL Server 2000 sysobjects uid column from the 2005+ sys.objects (or another) view?

I am trying to convert this code, as we are getting rid of all sysobjects references.

DECLARE GETOBJECTS CURSOR FOR 
SELECT NAME, UID FROM SYSOBJECTS
WHERE TYPE IN ('U','P')
    AND UID <> 1
    AND LOWER(RTRIM(NAME)) NOT LIKE 'PB%'

Thanks in advance,

Kevin

Upvotes: 1

Views: 6222

Answers (2)

Sparky
Sparky

Reputation: 15105

UID refers to the User ID of the owner of the object...

In SQL 2005 and SQl 2008, you can find it in

sys.sysobjects

field name UID

Sys.sysobjects

UID =1 is the dbo user.

So to use the routines and tables views and filter out dbo, try the following code

select Routine_name as Name, specific_schema as user 
from INFORMATION_SCHEMA.ROUTINES
where specific_schema <> 'dbo'
UNION
select Table_name as Name, specific_schema as user 
from INFORMATION_SCHEMA.TABLES
where specific_schema <> 'dbo'

Upvotes: 2

user596075
user596075

Reputation:

To get all objects that aren't owned by the dbo principal, do something like this:

select name, object_id
from sys.objects
where principal_id not in
(
    select principal_id
    from sys.database_principals
    where name = 'dbo'
)

Upvotes: 0

Related Questions