Posted By

michanne on 06/09/11

Tagged

Versions (?)

Last Edited at 06/09/11 03:35am

Statistics

Viewed 383 times

Favorited by 0 user(s)

Related snippets

Lots of Table Info

/ Published in: SQL

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

Highlighted TextExpand

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]

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.