获取表信息代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】获取表信息

作者:Dezai.CN / 发布于2011/6/13/ 499

<div>select top 10 @@ServerName as ServerName,
 db_name() as [database],
 object_schema_name(t.object_id) as [schema],
 --object_name(t.object_id) as [name],
 (select count(1) from sys.columns as c where c.object_id = t.object_id) as [Columns],
 (select name 
 from sys.database_principals 
 where principal_id = isnull(t.principal_id,(select principal_id 
 from sys.schemas as s 
 where s.schema_id = t.schema_id))
 ) as [owner],
 t.*,
 s.*
 from sys.tables as t
 join (select object_id,
 --count(partition_id)
 count(distinct index_id) as indexes,
 max(case when index_id = 0 then 'Heap' 
 when index_id = 1 then 'Clustered'
 else ''
 end) as HOBT,
 max(partition_number) as partitions,
 sum(in_row_data_page_count)*8192 as in_row_data_page_bytes,
 sum(in_row_used_page_count)*8192 as in_row_used_page_bytes,
 sum(in_row_reserved_page_count)*8192 as in_row_reserved_page_bytes,
 sum(lob_used_page_count)*8192 as lob_used_page_bytes,
 sum(lob_reserved_page_count)*8192 as lob_reserved_page_bytes,
 sum(row_overflow_used_page_count)*8192 as row_overflow_used_page_bytes,
 sum(row_overflow_reserved_page_count)*8192 as row_overflow_reserved_page_bytes,
 sum(used_page_count)*8192 as used_page_bytes,
 sum(reserved_page_count)*8192 as reserved_page_bytes,
 sum(case when index_id < 2 then row_count else 0 end) as rows
 from sys.dm_db_partition_stats as x
 group by object_id 
 ) as s
 on s.object_id = t.object_id 
 order by used_page_bytes desc, [schema],[name]</div>

试试其它关键字

　表信息　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)