(DIR) Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
(HTM) https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
(DIR) Return to: Scripts
*****************************************************
#Post#: 131--------------------------------------------------
Database Dictionary SP
By: srinivasma_exceldbp Date: January 2, 2015, 4:38 am
---------------------------------------------------------
-- The spGenerateDataDictionary Stored Procedure
---------------------------------
-- [spGenerateDataDictionary]
---------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[spGenerateDataDictionary]') AND type in
(N'P', N'PC'))
DROP PROCEDURE [dbo].[spGenerateDataDictionary]
GO
CREATE PROCEDURE [dbo].[spGenerateDataDictionary]
@SchemaName VARCHAR(128) = '[All]', -- Can Set to a
Particular Schema Name or Leave Set to '[All]' For ALL Schemas
@TableName VARCHAR(128) = '[All]' -- Set to a Particular
Table Name, or Leave Set to '[All]' for ALL Tables
AS
BEGIN
----------------------------------------------------------------
------------------------
-- Purpose: Generates a Data Dictionary for a SQL Server
Database
--
-- Shows '[Missing Definition]' For Objects That
Do Not Have a Data Dictionary
-- Description Defined.
--
--
--
-- Database:
-- [Varies]
--
-- Sample Calls:
--
-- -- Show Results for All Schemas and All Tables In
Database
-- EXEC [dbo].[spGenerateDataDictionary];
--
-- -- Show Results for All Tables in the
"HumanResources" Schema
-- EXEC [dbo].[spGenerateDataDictionary]
'HumanResources';
--
-- -- Show Results for the "Department" Table in the
"HumanResources" Schema Only
-- EXEC [dbo].[spGenerateDataDictionary]
'HumanResources', 'Department';
--
--
-- Tables Affected:
-- [Varies]
--
----------------------------------------------------------------
--------------------
SET NOCOUNT ON
----------------------------------------------------
-- STEP 1. Create A Table Variable to Hold Results
----------------------------------------------------
DECLARE @DataDictionary TABLE
(
[SchemaName] VARCHAR(128) NOT NULL DEFAULT(''),
[TableName] VARCHAR(128) NOT NULL DEFAULT(''),
[ColumnName] VARCHAR(128) NOT NULL DEFAULT('N/A'),
[XType] VARCHAR(8) NOT NULL DEFAULT(''),
[Description] VARCHAR(8000) NOT NULL DEFAULT(''),
[DataType] VARCHAR(128) NOT NULL DEFAULT('N/A'),
[Length] INT NOT NULL DEFAULT(0),
[IsNull] VARCHAR(5) NOT NULL DEFAULT('N/A')
)
------------------------------------------------------
-- STEP 2A. Populate Table
-- Include a Row for Each Table Column
------------------------------------------------------
INSERT INTO @DataDictionary
(
[SchemaName] ,
[TableName] ,
[ColumnName] ,
[XType] ,
[DataType] ,
[Length] ,
[IsNull] ,
[Description]
)
SELECT
s.name
AS 'SchemaName' ,
t.name
AS 'TableName' ,
c.name
AS 'ColumnName' ,
ISNULL(X.XType, '')
AS 'XType' ,
UPPER(ISNULL(y.name, '[N/A]'))
AS 'DataType' ,
ISNULL(c.max_length, 0)
AS 'MaxLength' ,
CASE
WHEN c.is_nullable = 0 THEN 'N'
ELSE 'Y'
END
AS 'IsNull' ,
CONVERT(VARCHAR(8000), ISNULL(ep.value, '[Missing
Definition]')) AS 'Description'
FROM sys.tables t WITH (NOLOCK)
INNER JOIN sys.schemas s WITH (NOLOCK) ON s.[schema_id]
= t.[schema_id]
INNER JOIN sys.columns c WITH (NOLOCK) ON c.[object_id]
= t.[object_id]
INNER JOIN sys.types y WITH (NOLOCK) ON y.[system_type_id]
= c.[system_type_id]
AND y.[user_type_id]
= c.[user_type_id]
LEFT OUTER JOIN
(
----------------------------------------------------------------
------------
-- This Inner Query Gets a List of the Columns With
Special Key Attributes
----------------------------------------------------------------
------------
SELECT U.TABLE_SCHEMA AS 'SchemaName',
U.TABLE_NAME AS 'TableName',
U.COLUMN_NAME AS 'ColumnName',
CASE
WHEN BOTHKEYS.TABLE_NAME IS NOT NULL THEN 'PK,
FK' -- Both a Primary Key and a Foreign Key!
ELSE
REPLACE(MAX(O.xtype), 'F', 'FK') -- Map 'F' --> 'FK'
END AS 'XType'
FROM [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] U
WITH (NOLOCK)
INNER JOIN sys.sysobjects O
WITH (NOLOCK) ON O.name = U.CONSTRAINT_NAME
LEFT OUTER JOIN
(
-- This Inner Query Gets a List of the Columns that
Are Both Primary Key (PK) and Foreign Key (FK)
SELECT CCU.TABLE_SCHEMA,
CCU.TABLE_NAME ,
CCU.COLUMN_NAME
FROM [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE]
CCU WITH (NOLOCK)
INNER JOIN sys.sysobjects SO
WITH (NOLOCK) ON SO.name = CCU.CONSTRAINT_NAME
WHERE SO.xtype IN ('F', 'PK') -- Look for Foreign
Keys or Primary Keys Only!
GROUP BY CCU.TABLE_SCHEMA,
CCU.TABLE_NAME ,
CCU.COLUMN_NAME
HAVING COUNT(*) > 1
) BOTHKEYS
ON BOTHKEYS.TABLE_SCHEMA = U.TABLE_SCHEMA
AND BOTHKEYS.TABLE_NAME = U.TABLE_NAME
AND BOTHKEYS.COLUMN_NAME = U.COLUMN_NAME
WHERE O.xtype IN ('F', 'PK') -- Look for Foreign Keys or
Primary Keys Only!
GROUP BY U.TABLE_SCHEMA,
U.TABLE_NAME ,
U.COLUMN_NAME ,
BOTHKEYS.TABLE_NAME
) X ON X.SchemaName = s.name AND X.TableName = t.name AND
X.ColumnName = c.name
LEFT OUTER JOIN sys.extended_properties ep WITH (NOLOCK)
ON ep.major_id = t.[object_id]
AND ep.minor_id = c.column_id
AND ep.class = 1 -- Class of Item = 'Object or Column'
WHERE
-- Table Name Filter
(@TableName = '[All]' OR t.name = @TableName)
AND
-- Schema Name Filter
(@SchemaName = '[All]' OR s.name = @SchemaName);
----------------------------------------------------
-- STEP 2B. Populate Temp Table
-- Include a Row for Each Table Itself
----------------------------------------------------
INSERT INTO @DataDictionary
(
[SchemaName] ,
[TableName] ,
[ColumnName] ,
[XType] ,
[DataType] ,
[Length] ,
[IsNull] ,
[Description]
)
SELECT
s.name
AS 'SchemaName' ,
t.name
AS 'TableName' ,
'[N/A]'
AS 'ColumnName' ,
'U'
AS 'XType' ,
'[N/A]'
AS 'DataType' ,
0
AS 'MaxLength' ,
'N/A'
AS 'IsNull' ,
CONVERT(VARCHAR(8000), ISNULL(ep.value, '[Missing
Definition]')) AS 'Description'
FROM sys.tables t WITH (NOLOCK)
INNER JOIN sys.schemas s WITH (NOLOCK) ON
s.[schema_id] = t.[schema_id]
LEFT OUTER JOIN sys.extended_properties ep WITH (NOLOCK)
ON ep.major_id = t.[object_id]
AND ep.minor_id = 0 -- 0 = Object (Table)
AND ep.class = 1 -- Class of Item = 'Object or Column'
WHERE
-- Table Name Filter
(@TableName = '[All]' OR t.name = @TableName)
AND
-- Schema Name Filter
(@SchemaName = '[All]' OR s.name = @SchemaName);
--------------------------------
-- STEP 3. Return Data To User
--------------------------------
SELECT
@@SERVERNAME AS 'ServerName' ,
DB_NAME() AS 'DatabaseName',
SchemaName AS 'Schema' ,
TableName AS 'TableName' ,
ColumnName AS 'ColumnName' ,
XType AS 'XType' ,
UPPER(DataType) AS 'DataType' ,
[Length] AS 'MaxLength' ,
[IsNull] AS 'IsNull' ,
[Description] AS 'Description'
FROM @DataDictionary
ORDER BY @@SERVERNAME,
DatabaseName,
SchemaName,
TableName,
XType DESC,
ColumnName;
END
GO
*****************************************************