Posted By

michanne on 06/09/11


Tagged

administration sqlserver


Versions (?)

Lots of Table Info


 / Published in: SQL
 

SQL Server - everything you want to know (or not) about a table.

  1. SELECT top 10 @@ServerName AS ServerName,
  2. db_name() AS [DATABASE],
  3. object_schema_name(t.object_id) AS [schema],
  4. --object_name(t.object_id) as [name],
  5. (SELECT count(1) FROM sys.COLUMNS AS c WHERE c.object_id = t.object_id) AS [COLUMNS],
  6. (SELECT name
  7. FROM sys.database_principals
  8. WHERE principal_id = isnull(t.principal_id,(SELECT principal_id
  9. FROM sys.schemas AS s
  10. WHERE s.schema_id = t.schema_id))
  11. ) AS [owner],
  12. t.*,
  13. s.*
  14. FROM sys.TABLES AS t
  15. JOIN (SELECT object_id,
  16. --count(partition_id)
  17. count(DISTINCT index_id) AS indexes,
  18. max(case when index_id = 0 then 'Heap'
  19. when index_id = 1 then 'Clustered'
  20. else ''
  21. end) AS HOBT,
  22. max(partition_number) AS partitions,
  23. sum(in_row_data_page_count)*8192 AS in_row_data_page_bytes,
  24. sum(in_row_used_page_count)*8192 AS in_row_used_page_bytes,
  25. sum(in_row_reserved_page_count)*8192 AS in_row_reserved_page_bytes,
  26. sum(lob_used_page_count)*8192 AS lob_used_page_bytes,
  27. sum(lob_reserved_page_count)*8192 AS lob_reserved_page_bytes,
  28. sum(row_overflow_used_page_count)*8192 AS row_overflow_used_page_bytes,
  29. sum(row_overflow_reserved_page_count)*8192 AS row_overflow_reserved_page_bytes,
  30. sum(used_page_count)*8192 AS used_page_bytes,
  31. sum(reserved_page_count)*8192 AS reserved_page_bytes,
  32. sum(case when index_id < 2 then row_count else 0 end) AS rows
  33. FROM sys.dm_db_partition_stats AS x
  34. GROUP BY object_id
  35. ) AS s
  36. ON s.object_id = t.object_id
  37. ORDER BY used_page_bytes DESC, [schema],[name]

Report this snippet  

You need to login to post a comment.