Data Dictionary

karthison, 2018-11-15 (first published: 2018-11-08)

Set the variable values, entering server name, database name, table name open query and debug, shown below..

@Server NVARCHAR(1000) = ''/*can be blank for non openquery*/
@Database NVARCHAR(1000) = ''/*cannot be blank*/
@TableName NVARCHAR(1000) = ''/*cannot be blank*/
@OpenQuery BIT= 0/*1 will use open query, 0 will not*/
@Debug BIT = 0 /*1 with Print @sql, 0 will execute @sql*/
and execute.
    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	
	@Server	NVARCHAR(1000) = ''/*can be blank for non openquery*/
,	@Database	NVARCHAR(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;

Rate

1 (2)

Share

Share

Rate

1 (2)