Lots of Table Info


/ Published in: SQL
Save to your folder(s)

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


Copy this code and paste it in your HTML
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.