系统表学习一

【环境】

MS Sql 2000

 

PS：Ms Sql 2005同样有以下系统表，但兼容包含此 Microsoft SQL Server 2000 系统表体现了向后兼容性。 建议您改用目录视图。

 

【系统表】

1、

sysobjects
在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行。只有在 tempdb 内，每个临时对象才在该表中占一行。

 

常用字段：
name
--sysname
--对象名
Id
--int
--对象标识号
xtype
--char(2)
--对象类型。可以是下列对象类型中的一种：
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束（类型是 K）
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束（类型是 K）
V = 视图
X = 扩展存储过程
 
uid
--smallint
--所有者对象的用户 ID。

type
--char(2)
--对象类型。可以是下列值之一：同xtype

 

 

/*
sys.objects 视图(注意不是表，是视图)
在数据库中创建的每个用户定义的架构范围内的对象在该表中均对应一行。 

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


可以将 OBJECT_ID()、OBJECT_NAME 和 OBJECTPROPERTY() 内置函数应用于在 sys.objects 中显示的对象。
*/

/*
常用字段：

列名
--数据类型
--说明  

name
--sysname
--对象名称。
 
object_id
--int
--对象标识号。在数据库中是唯一的。
 
schema_id
--int
--包含该对象的架构的 ID。

对于 SQL Server 2005 附带的所有架构范围内的系统对象，该值将始终为

IN (schema_id('sys'), schema_id('INFORMATION_SCHEMA')。
 
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 约束
 
parent_object_id
--int
--此对象所属对象的 ID。 

0 = 不是子对象。
 
type
--char(2)
--对象类型：

AF = 聚合函数 (CLR)

C = CHECK 约束

D = DEFAULT（约束或独立）

F = FOREIGN KEY 约束

PK = PRIMARY KEY 约束

P = SQL 存储过程

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

FN = SQL 标量函数

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

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

R = 规则（旧式，独立）

RF = 复制筛选过程

SN = 同义词

SQ = 服务队列

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

TR = SQL DML 触发器 

IF = SQL 内联表值函数

TF = SQL 表值函数

U = 表（用户定义类型）

UQ = UNIQUE 约束

V = 视图

X = 扩展存储过程

IT = 内部表
 
type_desc
--nvarchar(60)
--对象类型的说明。 

AGGREGATE_FUNCTION

CHECK_CONSTRAINTS

DEFAULT_CONSTRAINT

FOREIGN_KEY_CONSTRAINT

PRIMARY_KEY_CONSTRAINT

SQL_STORED_PROCEDURE

CLR_STORED_PROCEDURE

SQL_SCALAR_FUNCTION

CLR_SCALAR_FUNCTION

CLR_TABLE_VALUED_FUNCTION

RULE

REPLICATION_FILTER_PROCEDURE

SYNONYM

SERVICE_QUEUE

CLR_TRIGGER

SQL_TRIGGER

SQL_INLINE_TABLE_VALUED_FUNCTION

SQL_TABLE_VALUED_FUNCTION

USER_TABLE

UNIQUE_CONSTRAINT

VIEW

EXTENDED_STORED_PROCEDURE

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

*/

 

 

 

2、

syscolumns
每个表和视图中的每列在表中占一行，存储过程中的每个参数在表中也占一行。该表位于每个数据库中。

 

常用字段：
name
--sysname
--列名或过程参数的名称。
id
--int
--该列所属的表对象 ID，或与该参数关联的存储过程 ID。
colid
--smallint
--列或参数 ID。

 

 

/*
sys.columns

为包含列的对象（如视图或表）的每列返回一行。下面是包含列的对象类型的列表。

表值程序集函数 (FT)
内联表值 SQL 函数 (IF)
内部表 (IT)
系统表 (S)
表值 SQL 函数 (TF)
用户表 (U)
视图 (V)
*/

/*
常用字段：

object_id
--int
--此列所属对象的 ID。
 
name
--sysname
--列名。在对象中是唯一的。
 
column_id
--int
--列的 ID。在对象中是唯一的。
注意：  
列 ID 不能按顺序排列。
*/

 

 

 

3、

sysindexkeys
包含索引中的键或列的信息。该表存储在每个数据库中。

 

常用字段：
id
--int
--表 ID
indid
--smallint
--索引 ID
colid
--smallint
--列 ID
keyno
--smallint
--该列在索引中的位置

 

 

/*
sys.index_columns

属于 sys.indexes 索引或未排序的表（堆）的每个列都对应一行。

常用字段：

object_id
--int
--定义了索引的对象的 ID。
 
index_id
--int
--定义了列的索引的 ID。
 
index_column_id
--int
--索引列的 ID。index_column_id 仅在 index_id 内是唯一的。
 
column_id
--int
--object_id 中的列的 ID。 

0 = 非聚集索引中的行标识符 (RID)。

column_id 仅在 object_id 中是唯一的。
 
key_ordinal
--tinyint
--键列集内的序数 （从 1 开始）。

0 = 非键列，或者是 XML 索引。

由于 xml 类型的列不可比较，因此 XML 索引不会导致对基础列值排序。因为 XML 索引不是键，所以 key_ordinal 值将始终是 0。
 

*/

 

 

 

4、

sysindexes
数据库中的每个索引和表在表中各占一行。该表存储在每个数据库中。

 

常用字段：
id
--int
--表 ID（如果 indid = 0 或 255）。否则为索引所属表的 ID。
indid
--smallint
--索引 ID：
1 = 聚集索引
>1 = 非聚集
255 = 具有 text 或 image 数据的表条目
 
used
--int
--如果 indid = 0 或 indid = 1，used 是用于所有索引和表数据的总页数。如果 indid = 255，used 是用于 text 或 image 数据的页计数。否则是用于索引的页计数。
impid
--smallint
--保留。索引实现标志。
name
--sysname
--表名（如果 indid = 0 或 255）。否则为索引的名称。

 

 

/*
sys.indexes 

每个表格对象（例如，表、视图或表值函数）的索引或堆都包含一行。

常用字段：
object_id
--int
--该索引所属对象的 ID。
 
name
--sysname
--索引的名称。name 只在该对象中是唯一的。 
NULL = 堆

index_id
--int
--索引的 ID。index_id 只在该对象中是唯一的。 
0 = 堆
1 = 聚集索引
> 1 = 非聚集索引
 
type
--tinyint
--索引的类型：
0 = 堆
1 = 聚集
2 = 非聚集
3 = XML
 
type_desc
--nvarchar(60)
索引类型的说明：
HEAP
CLUSTERED
NONCLUSTERED
XML
*/

 

 

 

【实践问题】

Select a.id ,a.indid ,a.name from sysindexes a inner join sysindexkeys b on a.id = b.id and a.indid = b.indid  and b.id = object_id(@L_table)

 

其中，@L_table定义表。

 

设计表结构A，A的索引只有聚合索引，此聚合索引为外键。

使用上面语句会出现_WA_Sys_开头的索引

 

查询资料，如下：

When SQL Server generates statistics, it uses sysindexes to hold entries, and these are named "_WA_Sys_*". The 'status' column should distinguish them from 'normal' indexes.

 

此段意思说status可以区分。

 

根据实际，我观察到使用FirstIAM ,used可以观察出是否使用过------暂时可以这么认为：used = 0 Or FirstIAM = 0x0的都是"_WA_Sys_*"