sys.sysobjects、sys.all_objects、sys.objects三者区别

最新推荐文章于 2024-06-11 13:44:50 发布

转载最新推荐文章于 2024-06-11 13:44:50 发布 · 1.2w 阅读

文章标签：

#schema #sql server #object #database #数据库 #authorization

SQL Server 专栏收录该内容

9 篇文章

订阅专栏

本文深入解析SQLServer2005系统视图sys.sysobjects、sys.all_objects和sys.objects的区别与联系，包括它们的结构、权限、函数以及示例用法，帮助读者理解SQLServer对象管理的基本概念。

Sql server 2005系统视图sys.sysobjects和sys.all_objects,sys.objects，三者之间有什么区别?

一、这三个视图都是存在于SQL Server 2005的每个数据库中。在SQL Server 2000中，它们都是系统表，而不是视图。

关于两个版本中系统表和系统的视图的对应关系，参考：点击打开链接

1.sys.all_objects：显示所有架构范围内的用户定义对象和系统对象，参考点击打开链接

2.sys.sysobjects,sys.objects：用户在数据库中创建的每个对象(例如约束、默认值、日志、规则以及存储过程)都对应一行，参考点击打开链接

3.sys.objects 不显示 DDL 触发器，因为它们不是架构范围内的对象。所有触发器（包括 DML 和 DDL）均位于sys.triggers 中。sys.triggers 支持对各种触发器应用混合名称范围规则。

在自己的数据库中测试发现，sys.objects，sys.sysobjects视图的内容是完全一致的。这三个视图的结构比较类似，其中饱含type，id等属性。其中type的取值代表了各种不同的对象类型。对于触发器或者约束对象，parent_obj属性表示父表的id。

二、Name, ID相关的系统函数：
1.SCHEMA_NAME ( [ schema_id ] )：返回与架构 ID 关联的架构名称。
2.OBJECT_NAME ( object_id [, database_id ] )：返回架构范围内对象的数据库对象名称。
3.OBJECT_ID (object_name [,'object_type' ] )：返回架构范围内对象的数据库对象标识号，包括触发器等；若要返回 DDL 触发器的对象标识号，请使用 SELECT OBJECT_ID FROM　sys.triggers WHERE name = 'DatabaseTriggerLog'。

三、列名数据类型说明
name
sysname
对象名称。

object_id
int
对象标识号。在数据库中是唯一的。

principal_id
int
如果不是架构所有者，则为单个所有者的 ID。默认情况下，架构包含的对象由架构所有者拥有。不过，通过使用 ALTER AUTHORIZATION 语句更改所有权可以指定备用所有者。如果没有备用的单个所有者，则为 NULL。

如果对象类型为下列类型之一，则为 NULL：

C = CHECK 约束

D = DEFAULT（约束或独立）

F = FOREIGN KEY 约束

PK = PRIMARY KEY 约束

R = 规则（旧式，独立）

TA = 程序集（CLR 集成）触发器

TR = SQL 触发器

UQ = UNIQUE 约束

schema_id
int
包含该对象的架构的 ID。始终包含在 sys 或 INFORMATION_SCHEMA 架构中的架构范围内的系统对象。

parent_object_id
int
此对象所属对象的 ID。0 = 不是子对象。

type
char(2)
对象类型：

AF = 聚合函数 (CLR)

C = CHECK 约束

D = DEFAULT（约束或独立）

F = FOREIGN KEY 约束

FN = SQL 标量函数

FS = 程序集 (CLR) 标量函数

FT = 程序集 (CLR) 表值函数

IF = SQL 内联表值函数

IT = 内部表

P = SQL 存储过程

PC = 程序集 (CLR) 存储过程

PG = 计划指南

PK = PRIMARY KEY 约束

R = 规则（旧式，独立）

RF = 复制筛选过程

S = 系统基表

SN = 同义词

SQ = 服务队列

TA = 程序集 (CLR) DML 触发器

TF = SQL 表值函数

TR = SQL DML 触发器

U = 表（用户定义类型）

UQ = UNIQUE 约束

V = 视图

X = 扩展存储过程

type_desc
nvarchar(60)
对对象类型的说明：

AGGREGATE_FUNCTION

CHECK_CONSTRAINT

DEFAULT_CONSTRAINT

FOREIGN_KEY_CONSTRAINT

SQL_SCALAR_FUNCTION

CLR_SCALAR_FUNCTION

CLR_TABLE_VALUED_FUNCTION

SQL_INLINE_TABLE_VALUED_FUNCTION

INTERNAL_TABLE

SQL_STORED_PROCEDURE

CLR_STORED_PROCEDURE

PLAN_GUIDE

PRIMARY_KEY_CONSTRAINT

RULE

REPLICATION_FILTER_PROCEDURE

SYSTEM_TABLE

SYNONYM

SERVICE_QUEUE

CLR_TRIGGER

SQL_TABLE_VALUED_FUNCTION

SQL_TRIGGER

USER_TABLE

UNIQUE_CONSTRAINT

VIEW

EXTENDED_STORED_PROCEDURE

create_date
datetime
对象的创建日期。

modify_date
datetime
上次使用 ALTER 语句修改对象的日期。如果对象为表或视图，则创建或修改表或视图的聚集索引时，modify_date 也会随之更改。

is_ms_shipped
bit
对象由内部 SQL Server 组件创建。

is_published
bit
对象为发布对象。

is_schema_published
bit
仅发布对象的架构。

四、备注
可将 OBJECT_ID 、OBJECT_NAME 和 OBJECTPROPERTY () 内置函数应用于 sys.objects 中显示的对象。

此视图有一个具有相同架构的版本，名为 sys.system_objects ，该版本显示系统对象。还有另一个同时显示系统对象和用户对象的视图，名为 sys.all_objects 。所有这三个目录视图的结构都相同。

在此版本的 SQL Server 中，扩展索引（例如 XML 索引或空间索引）将视为 sys.objects 中的内部表（type = IT，type_desc = INTERNAL_TABLE）。对于扩展索引：

name 是索引表的内部名称。
parent_object_id 是基表的 object_id。
is_ms_shipped、is_published 和is_schema_published 列设置为 0。

五、权限
在 SQL Server 2005 及更高版本中，目录视图中仅显示用户拥有的安全对象的元数据，或用户对其拥有某些权限的安全对象的元数据。有关详细信息，请参阅元数据可见性配置。

六、示例
A. 返回在最近 N 天内修改过的所有对象
运行以下查询之前，请使用有效值替换 <database_name> 和 <n_days> 。

复制代码
USE <database_name>;
GO
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - <n_days>
ORDER BY modify_date;
GO
B. 返回指定存储过程或函数的参数
运行以下查询之前，请使用有效名称替换 <database_name> 和 <schema_name.object_name> 。

复制代码
USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,o.name AS object_name
,o.type_desc
,p.parameter_id
,p.name AS parameter_name
,TYPE_NAME(p.user_type_id) AS parameter_type
,p.max_length
,p.precision
,p.scale
,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')
ORDER BY schema_name, o.object_name, p.parameter_id;
GO
C. 返回数据库中的所有用户定义函数
运行以下查询之前，请使用有效数据库名称替换 <database_name> 。

USE <database_name>;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO
D. 返回架构中每个对象的所有者。
运行以下查询之前，请使用有效名称替换所有的 <database_name> 和 <schema_name> 。

USE <database_name>;
GO
SELECT 'OBJECT' AS entity_type
,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
,name
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION
SELECT 'TYPE' AS entity_type
,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) ASowner_name
,name
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type
,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
,xsc.name
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
ON s.schema_id = xsc.schema_id
WHERE s.name = '<schema_name>';
GO