Data Dictionary

USE MASTER;
GO

/******************************************************************************************
*Use for Table Mapping.
*Can be use in two ways.
*Executed in current server or execute acrossed linked server using openquery.
*
*Enter variable values
*@TableName, @OpenQuery, @Debug
*SET
*@Server, @Database, @OpenQuery, @Debug to execute across linked server using openquery
*
******************************************************************************************//*******************************************************************
*Declare variables
*******************************************************************/DECLARE
@ServerNVARCHAR(1000) = ''/*can be blank for non openquery*/,@DatabaseNVARCHAR(1000) = ''/*cannot be blank*/,@TableName NVARCHAR(1000) = ''/*cannot be blank*/,@OpenQuery BIT= 1/*1 will use open query, 0 will not*/,@Debug BIT = 0 /*1 with Print @sql, 0 will execute @sql*/;

DECLARE
@sql NVARCHAR(MAX) = ''
;
/*******************************************************************
*Which query should execute
*******************************************************************/IF @OpenQuery = 1
GOTO Open_Query;
/*******************************************************************
*Format table names with single quotes
*******************************************************************/SELECT @TableName = REPLACE(@TableName,',',''',''')

IF @Debug = 1
PRINT @Tablename

SET@sql = '
SELECT
    schema_name        = table_data.TABLE_SCHEMA
,   table_name         = table_data.TABLE_NAME
,   table_description  = table_properties.value
,   column_name        = column_data.COLUMN_NAME
,   data_Type          = UPPER(CAST(DATA_TYPE AS VARCHAR))
,   data_length        = CASE
                             WHEN CAST(column_data.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) IS NULL
                                 THEN
                                 '' ''
                             ELSE
                                 CAST(column_data.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)
                         END
,   nullable           = LEFT(column_data.IS_NULLABLE, 1)
,   column_description = column_description.ColumnDescription
,   column_data.ORDINAL_POSITION
FROM
    ' + @Database + '.INFORMATION_SCHEMA.TABLES AS table_data
    INNER JOIN ' + @Database + '.INFORMATION_SCHEMA.COLUMNS AS column_data
        ON column_data.TABLE_NAME = table_data.TABLE_NAME
    LEFT JOIN ' + @Database + '.sys.extended_properties AS table_properties
        ON table_properties.major_id = OBJECT_ID(table_data.TABLE_SCHEMA + ''.'' + table_data.TABLE_NAME)
           AND  table_properties.minor_id = 0
           AND  table_properties.name = ''MS_Description''
    LEFT JOIN
    (
        SELECT
            sc.object_id
        ,   sc.column_id
        ,   sc.name
        ,   colProp.[value] AS ColumnDescription
        FROM
            ' + @Database + '.sys.columns AS sc
            INNER JOIN ' + @Database + '.sys.extended_properties colProp
                ON colProp.major_id = sc.object_id
                   AND  colProp.minor_id = sc.column_id
                   AND  colProp.name = ''MS_Description''
    )AS column_description
        ON column_description.object_id = OBJECT_ID(table_data.TABLE_SCHEMA + ''.'' + table_data.TABLE_NAME)
           AND  column_description.name = column_data.COLUMN_NAME
WHERE
    table_data.TABLE_TYPE = ''base table''
    AND table_data.TABLE_NAME IN (''' + @TableName + ''')
ORDER BY
    table_data.TABLE_NAME
,column_data.COLUMN_NAME;'

IF @Debug = 1
PRINT @sql

IF @Debug = 0
EXECUTE sys.sp_executesql @sql;

RETURN;
/******************************************************************************************
*Used for Table Mapping across linked server using open query
******************************************************************************************/Open_Query:

/*******************************************************************
*Format table names with single quotes
*******************************************************************/SELECT @TableName = REPLACE(@TableName,',',''''',''''')

IF @Debug = 1
PRINT @Tablename

SET@sql = '
SELECT *
FROM OPENQUERY(' + @Server + ',''
SELECT
    schema_name        = table_data.TABLE_SCHEMA
,   table_name         = table_data.TABLE_NAME
,   table_description  = table_properties.value
,   column_name        = column_data.COLUMN_NAME
,   data_Type          = UPPER(CAST(DATA_TYPE AS VARCHAR))
,   data_length        = CASE
                             WHEN CAST(column_data.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) IS NULL
                                 THEN
                                 '''' ''''
                             ELSE
                                 CAST(column_data.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)
                         END
,   nullable           = LEFT(column_data.IS_NULLABLE, 1)
,   column_description = column_description.ColumnDescription
,   column_data.ORDINAL_POSITION
FROM
    ' + @Database + '.INFORMATION_SCHEMA.TABLES AS table_data
    INNER JOIN ' + @Database + '.INFORMATION_SCHEMA.COLUMNS AS column_data
        ON column_data.TABLE_NAME = table_data.TABLE_NAME
    LEFT JOIN ' + @Database + '.sys.extended_properties AS table_properties
        ON table_properties.major_id = OBJECT_ID(table_data.TABLE_SCHEMA + ''''.'''' + table_data.TABLE_NAME)
           AND  table_properties.minor_id = 0
           AND  table_properties.name = ''''MS_Description''''
    LEFT JOIN
    (
        SELECT
            sc.object_id
        ,   sc.column_id
        ,   sc.name
        ,   colProp.[value] AS ColumnDescription
        FROM
            ' + @Database + '.sys.columns AS sc
            INNER JOIN ' + @Database + '.sys.extended_properties colProp
                ON colProp.major_id = sc.object_id
                   AND  colProp.minor_id = sc.column_id
                   AND  colProp.name = ''''MS_Description''''
    )AS column_description
        ON column_description.object_id = OBJECT_ID(table_data.TABLE_SCHEMA + ''''.'''' + table_data.TABLE_NAME)
           AND  column_description.name = column_data.COLUMN_NAME
WHERE
    table_data.TABLE_TYPE = ''''base table''''
    AND table_data.TABLE_NAME IN (''''' + @TableName + ''''')
ORDER BY
    table_data.TABLE_NAME
,column_data.COLUMN_NAME;'')'

IF @Debug = 1
PRINT @sql

IF @Debug = 0
EXECUTE sys.sp_executesql @sql;