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)
