Advanced Search

Lots of Table Info

/ Published in: SQL
Save to your folder(s)
SQL Server - everything you want to know (or not) about a table.

Expand | Embed | Plain Text

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.