Revision: 47494
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at June 9, 2011 03:35 by michanne
Initial Code
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]
Initial URL
Initial Description
SQL Server - everything you want to know (or not) about a table.
Initial Title
Lots of Table Info
Initial Tags
Initial Language
SQL