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)