SQL Server 一些实用的元数据查询语句
注：使用的是微软提供的AdventureWorks2017.bak
1.获取某表中所有字段
declare @s nvarchar(4000) = ''; --Object_ID: Schema.Table, Schema默认为dbo select @s = @s + [name] + ',' FROM sys.columns WHERE Object_ID = Object_ID(N'HumanResources.Department') ; set @s = left(@s,len(@s)-1); select @s;
补充：也可以直接将该表下面的列拖动到查询窗口。
2.获取所有含某字段的表
select [name] from AdventureWorks2017.[dbo].sysobjects where id in(select id from AdventureWorks2017.[dbo].syscolumns Where name='ModifiedDate')
3.获取所有表的行数
SELECT a.name, b.rows FROM sys.sysobjects AS a INNER JOIN sys.sysindexes AS b ON a.id = b.id WHERE (b.indid IN (0, 1)) AND (a.type = 'u') ORDER BY a.name, b.rows DESC
4.获取所有存储过程的创建时间和修改时间
SELECT name, create_date, modify_date FROM sys.objects WHERE type = 'P' ORDER BY modify_date DESC
补充：可以更改type获取表，视图等的创建时间和修改时间。
5.获取数据中所有索引的信息
set transaction isolation level read uncommitted; BEGIN WITH tx AS ( SELECT a.object_id ,b.name AS schema_name ,a.name AS table_name ,c.name as ix_name ,c.is_unique AS ix_unique ,c.type_desc AS ix_type_desc ,d.index_column_id ,d.is_included_column ,e.name AS column_name ,f.name AS fg_name ,d.is_descending_key AS is_descending_key ,c.is_primary_key ,c.is_unique_constraint ,g.avg_fragmentation_in_percent ,g.page_count ,h.row_count ,h.used_page_count ,h.reserved_page_count FROM sys.tables AS a --with (nolock) INNER JOIN sys.schemas AS b ON a.schema_id = b.schema_id AND a.is_ms_shipped = 0 INNER JOIN sys.indexes AS c ON a.object_id = c.object_id INNER JOIN sys.index_columns AS d ON d.object_id = c.object_id AND d.index_id = c.index_id INNER JOIN sys.columns AS e ON e.object_id = d.object_id AND e.column_id = d.column_id INNER JOIN sys.data_spaces AS f ON f.data_space_id = c.data_space_id INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS g ON (c.object_id = g.object_id and c.index_id = g.index_id) INNER JOIN sys.dm_db_partition_stats h ON h.object_id = c.object_id and h.index_id < 2 ) SELECT d.schema_name ,d.table_name ,d.ix_name d,ix_unique ,d.ix_type_desc ,d.ix_index_column_name ,d.ix_included_column_name ,d.avg_fragmentation_in_percent ,d.index_page_count ,d.table_row_count ,d.table_page_count ,d.table_reserved_page_count ,d.fg_name ,d.is_primary_key ,d.is_unique_constraint ,d.Drop_Index ,d.Create_Index FROM ( SELECT Drop_Index = CASE WHEN (a.is_primary_key = 1 OR a.is_unique_constraint = 1) THEN 'ALTER TABLE ' + a.table_name + ' DROP CONSTRAINT ' + a.ix_name ELSE 'DROP INDEX ' + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + ' ON ' + a.schema_name + '.' + a.table_name END ,Create_Index = CASE WHEN (a.is_primary_key = 1 OR a.is_unique_constraint = 1) THEN 'ALTER TABLE ' + a.table_name + ' ADD CONSTRAINT ' + a.ix_name + CASE WHEN a.is_primary_key = 1 THEN ' PRIMARY KEY' ELSE ' UNIQUE' END + '(' + indexColumns.ix_index_column_name + ')' ELSE 'CREATE ' + CASE WHEN a.ix_unique = 1 THEN 'UNIQUE ' ELSE '' END + a.ix_type_desc + ' INDEX [' + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + '] ON ' + a.schema_name + '.' + a.table_name + '(' + indexColumns.ix_index_column_name + ')' + CASE WHEN IncludeIndex.ix_included_column_name IS NOT NULL THEN ' INCLUDE (' + IncludeIndex.ix_included_column_name + ')' ELSE '' END + ' ON [' + a.fg_name +']' END ,CASE WHEN a.ix_unique = 1 THEN 'UNIQUE' END AS ix_unique ,a.ix_type_desc ,a.ix_name ,a.schema_name ,a.table_name ,indexColumns.ix_index_column_name ,IncludeIndex.ix_included_column_name ,a.avg_fragmentation_in_percent ,a.page_count index_page_count ,a.row_count table_row_count ,a.used_page_count table_page_count ,a.fg_name ,a.is_primary_key ,a.is_unique_constraint ,a.reserved_page_count table_reserved_page_count FROM ( SELECT DISTINCT ix_unique ,ix_type_desc ,ix_name ,schema_name ,table_name ,fg_name ,is_primary_key ,is_unique_constraint ,avg_fragmentation_in_percent ,page_count ,row_count ,used_page_count ,reserved_page_count FROM tx ) AS a OUTER APPLY ( SELECT ix_index_column_name = STUFF(( SELECT ',' + column_name + CASE WHEN is_descending_key = 1 THEN ' DESC' ELSE '' END FROM tx AS b WHERE schema_name = a.schema_name AND table_name=a.table_name AND ix_name=a.ix_name AND ix_type_desc=a.ix_type_desc AND fg_name=a.fg_name AND is_included_column=0 ORDER BY index_column_id FOR XML PATH('') ),1,1,'') )IndexColumns OUTER APPLY ( SELECT ix_included_column_name = STUFF(( SELECT ',' + column_name FROM tx AS b WHERE schema_name = a.schema_name AND table_name=a.table_name AND ix_name=a.ix_name AND ix_type_desc=a.ix_type_desc AND fg_name=a.fg_name AND is_included_column=1 ORDER BY index_column_id FOR XML PATH('') ), 1,1,'') )IncludeIndex ) d ORDER BY d.schema_name,d.table_name,d.ix_name ; END
