SQL Server2008存储结构之基本系统视图（转)

--数据库实例的概要情况

SELECT * FROM SYS.SERVERS 

WHERE SERVER_ID=0 

--兼容性视图SELECT * FROM SYS.SYSSERVERS

 

--各个数据库的详细信息

SELECT * FROM SYS.DATABASES 

--兼容性视图SELECT * FROM SYS.SYSDATABASES

 

--文件组的详细信息

SELECT * FROM SYS.FILEGROUPS  

--兼容性视图SELECT * FROM SYS.SYSFILEGROUPS

 

--各个数据库文件的详细信息

SELECT * FROM SYS.MASTER_FILES 

--兼容性视图SELECT * FROM SYS.SYSALTFILES

 

--当前数据库文件的详细信息

SELECT * FROM SYS.DATABASE_FILES 

--兼容性视图SELECT * FROM SYS.SYSFILES

 

--数据空间的详细情况，可以是文件组或分区方案

SELECT * FROM SYS.DATA_SPACES

 

关于数据库表的存储信息，通过以下系统表我们可以大致了解数据库表在数据库中是如何定义的。以下视图提供了基本的数据库对象信息。

--我们首先创建一张表和一些索引

CREATE TABLE dbo.test

(

id int IDENTITY(1,1) NOT NULL,

name char(100) NULL,

CONSTRAINT PK_test PRIMARY KEY CLUSTERED (id ASC)

)

CREATE NONCLUSTERED INDEX IX_test ON dbo.test(name)

 

--表和对象详细信息，根据表名称查询出object_id为

--事实上几乎所有的用户对象都出自于SYS.OBJECTS表

SELECT * FROM SYS.OBJECTS 

WHERE type_desc='USER_TABLE' AND NAME='TEST'

--兼容性视图SYSOBJECTS

--如果要查询与该表相关的其他所有对象，则可以执行以下语句

SELECT * FROM SYS.OBJECTS 

WHERE type_desc='USER_TABLE' AND NAME='TEST' OR

parent_object_id in

(SELECT object_id FROM SYS.OBJECTS 

WHERE type_desc='USER_TABLE' AND NAME='TEST')

 

--表字段详细信息，可以查询出相关column_id

SELECT * FROM SYS.COLUMNS 

WHERE OBJECT_ID=5575058 

--兼容性视图SYSCOLUMNS

 

--表索引详细情况,可以清楚的看到存在两个索引

SELECT * FROM SYS.INDEXES WHERE OBJECT_ID=5575058

--兼容性视图SYSINDEXES

 

--表分区情况，数据库中所有表和索引的每个分区在表中各对应一行

--此处可以看到该表有两个分区，聚集索引即表本身，还有一个是name的非聚集索引

--partition_id 即分区的ID

--hobt_id包含此分区的行的数据堆或B树的ID  

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID=5575058

 

--分配单元情况，数据库中的每个分配单元都在表中占一行

--该表只有和SYS.PARTITIONS配合使用才有意义

SELECT * FROM SYS.ALLOCATION_UNITS 

 

--SYS.ALLOCATION_UNITS和SYS.PARTITIONS一起使用能够反映出某个对象的页面分配和使用情况

SELECT * FROM SYS.ALLOCATION_UNITS U,SYS.PARTITIONS P

WHERE U.TYPE IN (1,3) AND U.CONTAINER_ID=P.HOBT_ID AND P.OBJECT_ID=5575058

UNION ALL

SELECT * FROM SYS.ALLOCATION_UNITS U,SYS.PARTITIONS P

WHERE U.TYPE =2 AND U.CONTAINER_ID=P.PARTITION_ID AND P.OBJECT_ID=5575058

 

--返回每个分区的页和行计数信息

SELECT * FROM SYS.DM_DB_PARTITION_STATS WHERE OBJECT_ID=5575058 

 

--返回索引的详细字段情况

SELECT * FROM SYS.INDEX_COLUMNS WHERE OBJECT_ID=5575058 

--兼容性视图SYSINDEXKEYS

 

--以下为根据某个索引名称获取其相关字段的语句

DECLARE @index_field_names VARCHAR(500)

SET @index_field_names='';

SELECT @index_field_names=@index_field_names+c.name+','

FROM SYS.INDEX_COLUMNS a,SYS.INDEXES b,SYS.COLUMNS c

WHERE a.object_id=b.object_id AND a.index_id=b.index_id 

AND a.object_id=c.object_id AND a.column_id=c.column_id

AND b.name='IX_test2'

ORDER BY a.index_column_id

SET @index_field_names=LEFT(@index_field_names,LEN(@index_field_names)-1)

PRINT @index_field_names

 

--CHECK约束，数据来源sys.objects.type = 'C'

SELECT * FROM SYS.CHECK_CONSTRAINTS WHERE OBJECT_ID=?

--兼容性视图SYSCONSTRAINTS

 

--数据来源sys.objects.type = D

SELECT * FROM SYS.DEFAULT_CONSTRAINTS WHERE OBJECT_ID=?

--兼容性视图SYSCONSTRAINTS

 

--主键或唯一约束,数据来源sys.objects.type PK 和UQ

SELECT * FROM SYS.KEY_CONSTRAINTS WHERE OBJECT_ID=?

--兼容性视图SYSCONSTRAINTS

 

--外键，数据来源sys.object.type = F

SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID=?  

--兼容性视图SYSREFERENCES

 

--触发器

SELECT * FROM SYS.TRIGGERS WHERE OBJECT_ID=?  

 

--注释

SELECT * FROM SYS.SQL_MODULES 

--兼容性视图SYSCOMMENTS

 

--数据库用户表

SELECT * FROM SYS.DATABASE_PRINCIPALS 

--兼容性视图SYSUSERS

 

--数据库数据类型表

SELECT * FROM SYS.TYPES 

--兼容性视图SYSTYPES