This is my favorite view on the blog.  It contains is an instance-wide list of tables, complete with table size in number of rows and number of bytes.

It does have many dependencies, all of which can be found on this blog.

                vwPartitionRaw

                vwDictCol

                SpaceUsed

                vwObjectFileGroup

         udfPadLeft()

                udfFormatInteger()

This object is referenced by various other objects in this blog.

USE Admin

IF Object_ID('dbo.vwTable') Is Not Null

      DROP VIEW dbo.vwTable

go

CREATE VIEW [dbo].[vwTable]

AS

/*    DATE        AUTHOR            REMARKS

      9/11/13           PPaiva            Initial creation.

      SELECT *

      FROM Admin.dbo.vwTable

      ORDER BY Rows desc

      SELECT TOP 100 *

      FROM Admin.dbo.vwTable

      WHERE DB = 'MyDB'

      ORDER BY 1, 2, 3, 4

*/

-- Get rows/table

WITH cteTP

AS (  SELECT Server, DB, SchemaName, Tablename, object_id, Sum(Rows) Rows, Count(*) NumPartitions

            FROM dbo.vwPartitionRaw

            GROUP BY  Server, DB, Tablename, SchemaName, object_id

      ),

-- Tables with number of columns

cteDC

AS (  SELECT  Server,

                        DB,

                        SchemaName,

                        ObjName TableName,

                        IsSystemDB,

                        ObjCreateDate,

                        ObjModifyDate,

                        object_id,

                        Count(*) NumCols,

                        Min(ViewCreateDate) ViewCreateDate

            FROM dbo.vwDictCol

            WHERE ObjType = 'Table'

            GROUP BY Server,

                        DB,

                        SchemaName,

                        ObjName,

                        IsSystemDB,

                        ObjCreateDate,

                        ObjModifyDate,

                        object_id

      ),

-- Calc BytesPerRow          

cteRowSize

AS (  SELECT Server, DB, object_id, Sum(Length) as BytesPerRow

            FROM dbo.vwDictCol

            WHERE ObjType = 'table'

            GROUP BY Server, DB, object_id

      ),

-- Contains

cteSpaceUsed

AS (  SELECT Server, DB, object_id, DataMB, IndexMB, TotalMB, ReservedMB, PopDate SpaceUsedPopDate

                  --SizeMB, Reserved, data, index_size

            FROM dbo.SpaceUsed

      ),

     

cteFileGroup

AS    (     SELECT Server, DB, ObjectID, FileGroup

            FROM dbo.vwObjectFileGroup

      )

-- Final query         

SELECT  tp.Server,

            tp.DB,

            fg.FileGroup,

            tp.SchemaName,

            tp.TableName,          

            Admin.dbo.udfPadLeft(

                                    Admin.dbo.udfFormatInteger(tp.Rows),

                                    15,

                                    ' '

                                    ) RowsFmt,

            Admin.dbo.udfFormatInteger(su.TotalMB)TotalMBFmt,

            tp.NumPartitions,

            dc.NumCols,

            dc.ObjCreateDate,

            dc.ObjModifyDate,

            su.DataMB,

            su.IndexMB,

            su.ReservedMB,

            tp.Rows,

            su.TotalMB,

            rs.BytesPerRow,

            Convert(decimal(10, 1), (rs.BytesPerRow * tp.Rows / 1000000000.0)) DataGBProjectedMax,

            dc.object_id,

            IsSystemDB,

            'Use [' + tp.db + '];  Exec sp_help ''[' + tp.SchemaName + '].[' + tp.TableName + ']''' spHelp,

            'Use [' + tp.db + '];  Exec sp_helpindex ''[' + tp.SchemaName + '].[' + tp.TableName + ']''' spHelpIndex,

            'TRUNCATE TABLE [' + tp.DB + '].[' + tp.SchemaName + '].[' + tp.TableName + ']' TruncateScript,

            'DROP TABLE [' + tp.DB + '].[' + tp.SchemaName + '].[' + tp.TableName + ']' DropScript,

            su.SpaceUsedPopDate,

            dc.ViewCreateDate vwDictColCreateDate

FROM cteTP tp

LEFT JOIN cteDC dc

      ON  tp.Server = dc.Server

      AND tp.DB = dc.DB

      AND tp.object_id = dc.object_id           -- object_id is unique only within a database

LEFT JOIN cteRowSize rs

      ON  rs.Server = tp.Server

      AND rs.DB = tp.DB

      AND rs.object_id = tp.object_id

LEFT JOIN cteSpaceUsed su

      ON  su.Server = tp.Server

      AND su.DB = tp.DB

      AND su.object_id = tp.object_id

LEFT JOIN cteFileGroup fg

      ON  fg.ObjectID = dc.Object_ID

      AND tp.Server = fg.Server

      AND tp.DB = fg.DB