Data Dictionary
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
@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;