相当于sysobjects.uid的SQL Server2008 sys.objects

Question

有人知道如何从sysobjects uid (或其他)视图中获得SQL Server2000 2005+ sys.objects列的等价物吗？

我正在尝试转换这段代码，因为我们正在摆脱所有的sysobjects引用。

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

提前谢谢你，

凯文

Answer 1

UID指的是对象所有者的用户ID ...

在SQL 2005和SQl 2008中，可以在

sys.sysobjects 

字段名称UID

Sys.sysobjects

UID =1是dbo用户。

因此，要使用例程和表视图并过滤出dbo，请尝试以下代码

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'

Answer 2

要获取不属于dbo主体的所有对象，请执行以下操作：

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