Qlserver provides a rich system view, from macro to micro, from static to dynamic response database object storage results, system performance, system wait events and so on. It also retains a view of compatibility with earlier versions, the main difference being that the new system view provided by SQLServer2008 is more comprehensive and rich, and two more about naming rules.
Almost all object information for SQLServer2008 is present in the sys.objects system view, while the corresponding copies are kept in different system views, for functions, views, stored procedures, triggers and other corresponding text objects, The details of the corresponding object are stored in the new Sys.sql_modules view.
For the storage structure at the database level, we can see the following views:
--Summary of database instances
Select*from SYS. SERVERS
WHERE server_id=0
--Compatibility View Select*from SYS. SYSSERVERS
--Detailed information for each database
Select*from SYS. DATABASES
--Compatibility View Select*from SYS. sysdatabases
--Detailed information about the filegroup
Select*from SYS. Filegroups
--Compatibility View Select*from SYS. Sysfilegroups
--Detailed information for each database file
Select*from SYS. Master_files
--Compatibility View Select*from SYS. Sysaltfiles
--Details about the current database file
Select*from SYS. Database_files
--Compatibility View Select*from SYS. Sysfiles
--Details of the data space, which can be a filegroup or partition scheme
Select*from SYS. Data_spaces
With regard to the storage information of database tables, we can get an overview of how database tables are defined in the database through the following system tables. The following views provide basic database object information.
#div_code img {border:0px none;} --We first create a table and some indexes
CREATE TABLE Dbo.test
(
Idintidentity (Notnull),
Name Char (+) NULL,
CONSTRAINT pk_test PRIMARY KEY CLUSTERED (IDASC)
)
CREATE nonclustered INDEX ix_testondbo.test (name)
--Table and object details, which are queried according to the name of the table object_id
-Virtually all user objects are derived from the sys.objects table
Select*from SYS. OBJECTS
WHERE type_desc= ' user_table ' and name= ' TEST '
--Compatibility View sysobjects
--If you want to query all other objects related to the table, you can execute the following statement
Select*from SYS. OBJECTS
WHERE type_desc= ' user_table ' and name= ' TEST ' OR
PARENT_OBJECT_ID in
(selectobject_id from SYS.) OBJECTS
WHERE type_desc= ' user_table ' and name= ' TEST ')
--table field details, you can query the relevant column_id
Select*from SYS. COLUMNS
WHERE object_id=5575058
--Compatibility View syscolumns
--table Index details, you can clearly see the existence of two indexes
Select*from SYS. INDEXES WHERE object_id=5575058
--Compatibility View sysindexes
--table partitioning, where each partition of all tables and indexes in the database corresponds to a row in the table
--Here you can see that the table has two partitions, the clustered index is the table itself, and a nonclustered index that is name
--PARTITION_ID is the ID of the partition
--HOBT_ID the ID of the data heap or B-tree that contains the rows for this partition
Select*from SYS. Partitions WHERE object_id=5575058
--allocation unit, where each allocation unit in the database occupies a row in the table
--The table only makes sense if it works with sys.partitions.
Select*from SYS. Allocation_units
--sys. Allocation_units and sys.partitions work together to reflect the page allocation and usage of an object
Select*from SYS. Allocation_units U,sys. Partitions P
WHERE U.type in (1,3) Andu. CONTAINER_ID=P.HOBT_IDANDP. object_id=5575058
UNION All
Select*from SYS. Allocation_units U,sys. Partitions P
WHERE U.type=2andu. CONTAINER_ID=P.PARTITION_IDANDP. object_id=5575058
--Returns page and row count information for each partition
Select*from SYS. Dm_db_partition_stats WHERE object_id=5575058
--Returns the detailed field of the index
Select*from SYS. Index_columns WHERE object_id=5575058
--Compatibility View Sysindexkeys
--The following is a statement that gets its related fields based on an index name
DECLARE @index_field_names VARCHAR (500)
[Email protected]_field_names= ';
[Email protected] [Email protected]_field_names+c.name+ ', '
From SYS. Index_columns A,sys. INDEXES B,sys. COLUMNS C
WHERE a.object_id=b.object_idanda.index_id=b.index_id
anda.object_id=c.object_idanda.column_id=c.column_id
Andb.name= ' Ix_test2 '
ORDER by a.index_column_id
[Email Protected]_field_names=left (@index_field_names, LEN (@index_field_names)-1)
PRINT @index_field_names
--check constraint, data source sys.objects.type= ' C '
Select*from SYS. Check_constraints WHERE object_id=?
--Compatibility View sysconstraints
--Data source Sys.objects.type=d
Select*from SYS. Default_constraints WHERE object_id=?
--Compatibility View sysconstraints
--PRIMARY KEY or UNIQUE constraint, data source Sys.objects.type PK and UQ
Select*from SYS. Key_constraints WHERE object_id=?
--Compatibility View sysconstraints
--foreign key, data source Sys.object.type=f
Select*from SYS. Foreign_keys WHERE object_id=?
--Compatibility View sysreferences
--Trigger
Select*from SYS. TRIGGERS WHERE object_id=?
--Notes
Select*from SYS. Sql_modules
--Compatibility View syscomments
--Database User table
Select*from SYS. Database_principals
--Compatibility View sysusers
--Database data type table
Select*from SYS. TYPES
--Compatibility View systypes
Common system views in SQL Server
