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

udfPadLeft()

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

USE Admin

IF OBJECT_ID('dbo.udfPadLeft') Is Not Null

      DROP FUNCTION dbo.udfPadLeft

GO

CREATE FUNCTION dbo.udfPadLeft(

      @In varchar(100),

      @Width int,

      @PadChar varchar(1)

      )

RETURNS varchar(100)

AS

/*    DATE        AUTHOR            REMARKS

      7/23/13           PPaiva            Initial creation.

     

      DESCRIPTION

            Pads @In with @PadChar so that the length of the returned

            value is @Width.  If the length of @In is greater than @Width

            then @In is returned. 

      USAGE

            SELECT dbo.udfPadLeft('7', '3', '0')

*/

BEGIN

      DECLARE @Out varchar(100),

                  @LenIn int

      SET @LenIn = Len(@In)

     

      IF @Width - @LenIn < 0

            SET @Out = @In

      ELSE

            SET @Out = REPLICATE(@PadChar, @Width - @LenIn) + @In

      RETURN @Out

END

This function simply formats an integer with commas to separate thousands, if necessary. 

This object is referenced by various other objects in this blog, including view vwTable which will be presented soon.

USE Admin

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

      DROP FUNCTION dbo.udfFormatInteger

go

CREATE FUNCTION dbo.udfFormatInteger

      (@In bigint)

RETURNS varchar(20)

AS

/*    DATE              AUTHOR            REMARKS    

      6/22/13           PPaiva            Initial creation.

     

      DESCRIPTION

            Formats a given integer with commas.

                  Examples:

                              IN                OUT

                              123               123

                            12345            12,345

                           -54321           -54,321

      USAGE

            SELECT dbo.udfFormatInteger(1234567890)

            SELECT dbo.udfFormatInteger(12345)

            SELECT dbo.udfFormatInteger(-54321)

            SELECT dbo.udfFormatInteger(0)

*/

BEGIN

      DECLARE @Out varchar(20),

                  @sIn varchar(20),

                  @Balance varchar(20),

                  @CurrTextLen smallint,

                  @IsNegative bit

      IF @In < 0

            BEGIN

                  SET @IsNegative = 1

                  SET @In = Abs(@In)

            END

      SET @sIn = Convert(varchar, @In)

      SET @CurrTextLen = Len(@sIn)

      SET @Out = ''

      SET @Balance = @sIn

      IF @CurrTextLen > 3

            BEGIN

                  WHILE 1 = 1

                        BEGIN

                              SET @Out = ',' + Right(@Balance, 3) + @Out

                              SET @Balance = Substring(@sIn, 1, @CurrTextLen - 3)

                              SET @CurrTextLen = Len(@Balance)

                              IF @CurrTextLen > 3

                                    CONTINUE

                              ELSE

                                    BEGIN

                                          SET @Out = @Balance + @Out

                                          BREAK

                                    END  

                        END

            END

      ELSE

            SET @Out = @sIn

      IF @IsNegative = 1

            SET @Out = '-' + @Out

      RETURN @Out

END

CreateVwDictCol

This proc creates view vwDictCol which is a column dictionary.  You could also use INFORMATION_SCHEMA.Columns.  This has the advantage of being instance-wide (all databases) rather than for only one database.  This view encompasses tables, views, functions, and procedures.

This object is referenced by view vwTable which will be presented soon.

USE Admin

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

      DROP PROC dbo.CreateVwDictCol

go

CREATE  PROC dbo.CreateVwDictCol

      @ShowSql bit = 0,

      @ShowSysObjects bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      5/1/13            PPaiva            Intial creation.

      DESCRIPTION

            Creates a view in the Admin database to see a column

                  dictionary of tables and other objects for all

                  databases in this server.

            System objects are excluded.

            Coded to use Convert(varchar, ServerProperty('ServerName'))

                  rather than @@ServerName since the latter can be erroneous.

      USAGE

            Exec Admin..CreateVwDictCol

            Exec Admin..CreateVwDictCol 1

            Exec Admin..CreateVwDictCol 1, 1

      DEBUG

            SELECT *

            FROM sys.databases

            SELECT *

            FROM Admin.dbo.vwDictCol

            WHERE DB = 'MyDB'

            -- Summary of Object Types

            SELECT ObjType, Count(*) Qty

            FROM Admin.dbo.vwDictCol

            GROUP BY ObjType

            ORDER BY 1

            -- Summary of Object Types per DB

            SELECT DB, ObjType, Count(*) Qty

            FROM Admin.dbo.vwDictCol

            GROUP BY DB, ObjType

            ORDER BY 1, 2

            -- Summary of rows/DB

            SELECT DB, Count(*) Qty

            FROM Admin.dbo.vwDictCol

            GROUP BY DB

            ORDER BY 1

            -- All views

            SELECT *

            FROM Admin.dbo.vwDictCol

            WHERE ObjType = 'View'

           

*/

SET NOCOUNT ON

DECLARE @s varchar(max),

            @DB varchar(100),

            @MaxDB varchar(100),

            @i int,

            @sNow varchar(16)

SET @sNow = Convert(varchar(16), GetDate(), 120)

SET @s = 'IF Object_ID(''dbo.vwDictCol'') Is Not Null

      DROP VIEW dbo.vwDictCol'

IF @ShowSql = 1

      Print @s

Exec(@s)

     

Print ''

SET @s = 'CREATE VIEW vwDictCol

AS

/*    DATE        AUTHOR            REMARKS

      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Initial creation.

      DESCRIPTION

            Provides a dictionary of columns for

                  tables

                  views

                  functions (columns for tables, parameters for scalar)

                  procedures (parameter)

            This view is auto-generated via execution of

                  Admin.dbo.CreateVwDictCol. 

            If a new database is added this view won''t show it unless

                  you run the refresh code below. 

            If a database is deleted this view will malfunction unless

                  you run the refresh code below. 

                 

      To REFRESH VIEW when new databases are added:

            Exec Admin.dbo.CreateVwDictCol

           

      -- Sample of view      

      SELECT TOP 100 *

      FROM Admin.dbo.vwDictCol

      -- Summary of Object Types on this instance

      SELECT ObjType, Count(*) Qty

      FROM Admin.dbo.vwDictCol

      GROUP BY ObjType

      ORDER BY 1

      -- Summary of Object Types per DB

      SELECT DB, ObjType, Count(*) Qty

      FROM Admin.dbo.vwDictCol

      GROUP BY DB, ObjType

      ORDER BY 1, 2

      -- Most objects per DB

      SELECT DB, Count(*) Qty

      FROM Admin.dbo.vwDictCol

      GROUP BY DB

      ORDER BY 2 desc

*/

'

SELECT  *

INTO #DBs

FROM sys.databases

WHERE state_desc = 'ONLINE'

 

SELECT  @MaxDB = Max(Name),

            @DB = '',

            @i = 0

FROM #DBs

DECLARE @IsSystemDB char(1)

WHILE @DB < @MaxDB

      BEGIN

            SET @i = @i + 1

            SELECT @DB = Min(Name)

            FROM #DBs

            WHERE Name > @DB

           

            IF @DB In ('master', 'tempdb', 'model', 'msdb', 'Admin', 'distribution')

                  SET @IsSystemDB = '1'

            ELSE

                  SET @IsSystemDB = '0'

            SET @s = @s + '

SELECT  '

            SET @s = @s + 'Convert(varchar, ServerProperty(''ServerName'')) as Server,

            ''' + @DB + ''' as DB,

            ot.DescShort ObjType,

            s.name COLLATE SQL_Latin1_General_CP1_CI_AS SchemaName,

            o.Name COLLATE SQL_Latin1_General_CP1_CI_AS ObjName,

            c.Name COLLATE SQL_Latin1_General_CP1_CI_AS ColName,

            c.Is_Computed IsCalc,

            c.Is_Identity IsIdentity,

            c.is_nullable IsNullable,

            c.column_id ColID,

            t.Name COLLATE SQL_Latin1_General_CP1_CI_AS Datatype,

            c.max_length Length,

            CASE WHEN t.Name COLLATE SQL_Latin1_General_CP1_CI_AS In (''char'', ''varchar'', ''nchar'', ''nvarchar'')

                        THEN t.Name COLLATE SQL_Latin1_General_CP1_CI_AS + ''('' + Convert(varchar, c.max_Length) + '')''

                   ELSE t.Name COLLATE SQL_Latin1_General_CP1_CI_AS

                  END   CodeDatatype,

            o.object_id,

            o.create_date ObjCreateDate,

            o.modify_date ObjModifyDate,

            ' + @IsSystemDB + ' IsSystemDB,

            ''' + @sNow + ''' ViewCreateDate

FROM [' + @DB + '].sys.objects o

LEFT JOIN [' + @DB + '].sys.columns c

      ON c.object_id = o.object_id

JOIN [' + @DB + '].sys.schemas s

      ON s.schema_id = o.schema_id

LEFT JOIN [' + @DB + '].sys.types t

      ON t.user_type_id = c.system_type_id

JOIN Admin.dbo.infraObjectType ot

      ON ot.xType = o.Type COLLATE SQL_Latin1_General_CP1_CI_AS

'

IF @ShowSysObjects = 0

      SET @s = @s + 'WHERE o.type <> ''s''

'

                                   

            IF @DB <> @MaxDB

                  SET @s = @s + '    UNION ALL '

                 

      END

IF @ShowSql = 1

      BEGIN

            Print @s

      END

Exec (@s)

CreateInfraObjectType

This proc creates table infraObjectType which is is then JOINed with various queries where a list of object types are needed.  I find it much cleaner to put this into a table that can be referenced, rather than putting all the information into a verbose CASE statement.  The contents of this table come from BOL (book online).

This object is referenced by view vwDictCol which will be presented soon.

USE Admin

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

      DROP PROC dbo.CreateInfraObjectType

go

CREATE PROC dbo.CreateInfraObjectType

      @Debug bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      4/4/13            PPaiva            Initial creation.

     

     

      DESCRIPTION

            Creates and populates table infraObjectType.

           

      USAGE

            CreateInfraObjectType 1

     

      DEBUG

            SELECT *

            FROM infraObjectType

*/

SET NOCOUNT ON

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

      DROP TABLE dbo.infraObjectType

CREATE TABLE dbo.infraObjectType (

      xType varchar(10) NOT NULL CONSTRAINT pk_infraObjectType PRIMARY KEY CLUSTERED,

      Type varchar(2) NOT NULL ,

      Description varchar(50) NOT NULL,

      DescShort varchar(50) NOT NULL

      )

INSERT INTO infraObjectType VALUES ('C', 'C', 'CHECK constraint', 'CheckCon')

INSERT INTO infraObjectType VALUES ('D', 'D', 'Default or DEFAULT constraint', 'DefaultCon')

INSERT INTO infraObjectType VALUES ('F', 'F', 'FOREIGN KEY constraint', 'FK')

INSERT INTO infraObjectType VALUES ('L', 'L', 'Log', 'Log')

INSERT INTO infraObjectType VALUES ('FN', 'FN', 'Scalar function', 'FunctionScalar')

INSERT INTO infraObjectType VALUES ('IF', 'IF', 'Inlined table-function', 'FunctionTable')

INSERT INTO infraObjectType VALUES ('P', 'P', 'Stored procedure', 'Proc')

INSERT INTO infraObjectType VALUES ('PK', 'K', 'PRIMARY KEY constraint', 'PK')

INSERT INTO infraObjectType VALUES ('(Not Used)', 'R', 'Rule', 'Rule')

INSERT INTO infraObjectType VALUES ('RF', 'FF', 'Replication filter stored procedure', 'RepFilterProc')

INSERT INTO infraObjectType VALUES ('S', 'S', 'System table', 'SysTable')

INSERT INTO infraObjectType VALUES ('TF', 'TF', 'Table function', 'FunctionTable')

INSERT INTO infraObjectType VALUES ('TR', 'TR', 'Trigger', 'Trigger')

INSERT INTO infraObjectType VALUES ('U', 'U', 'User table', 'Table')

INSERT INTO infraObjectType VALUES ('UQ', 'K', 'UNIQUE constraint', 'UniqCon')

INSERT INTO infraObjectType VALUES ('V', 'V', 'View', 'View')

INSERT INTO infraObjectType VALUES ('X', 'X', 'Extended stored procedure', 'ExProc')

IF @Debug = 1

      SELECT *

      FROM infraObjectType