First of a series on how to retrieve metadata from your tables
Chris Hayes is a software developer and DBA in the Dallas/Ft Worth area, and contributes this blog
This script is generates a simple data dictionary for the tables in the currently selected database. Basic information is provided for the columns in the database tables like:
- Schema
- Table Name
- Column Name
- The ordinal position of the column in the table
- Default values of the column
- Whether the column is nullable or not nullable
- Sql Server data type
- Maximum Length of the column
- Precision of the column (for numeric and related data types)
- Scale of the column (for numeric and related data types)
- Indexed indicates that the column is included in an index
This script is Part 1 in building a Sql Server Data Dictionary. Future installments will discuss adding descriptions to columns and tables via extended properties and adding additional database objects to the data dictionary for easier reference.
Chris Hayes
----------------------------
“Game over man. Game over…”
/**********************************Data Dictionary Simple**********************************/
SELECT
ISCOL.TABLE_CATALOG AS [DatabaseName]
, ISCOL.TABLE_SCHEMA AS [Schema]
, ISCOL.TABLE_NAME AS [TableName]
, ISCOL.COLUMN_NAME AS [ColumnName]
, ISCOL.ORDINAL_POSITION AS [Position]
, ISNULL( ISCOL.COLUMN_DEFAULT, '' ) AS [DefaultValue]
, CASE
WHEN ISCOL.IS_NULLABLE = 'YES' THEN 'Nullable'
ELSE 'Not Nullable'
END AS [Nullable]
, UPPER( ISCOL.DATA_TYPE ) AS [DataType]
, SYSCOL.max_length AS [MaximumLength]
, ISNULL( ISCOL.NUMERIC_PRECISION, '' ) AS [Precision]
, ISNULL( ISCOL.NUMERIC_SCALE, '' ) AS [Scale]
, (
CASE
WHEN SYSCOL.is_identity = 1
THEN
'IDENTITY'
+
(
CASE WHEN SYSIDCOL.SEED_VALUE <> 0 THEN ' Seed: ' + CONVERT( VARCHAR, SYSIDCOL.SEED_VALUE ) ELSE '' END
+
CASE WHEN SYSIDCOL.INCREMENT_VALUE <> 0 THEN ' Increment: '
+ ( CASE WHEN SYSIDCOL.INCREMENT_VALUE > 0 THEN '+' WHEN SYSIDCOL.INCREMENT_VALUE < 0 THEN '-' END )
+ CONVERT( VARCHAR, SYSIDCOL.INCREMENT_VALUE )
ELSE '' END
)
ELSE ''
END
)
AS [Identity]
, CASE WHEN INDX.ColumnID IS NOT NULL THEN 'Indexed' ELSE '' END AS [Indexed]
FROM INFORMATION_SCHEMA.columns AS ISCOL (NOLOCK)
INNER JOIN sys.columns AS SYSCOL (NOLOCK) ON SYSCOL.object_id = OBJECT_ID( ISCOL.TABLE_NAME )
AND SYSCOL.[name] = ISCOL.COLUMN_NAME
LEFT OUTER JOIN sys.identity_columns AS SYSIDCOL (NOLOCK) ON SYSIDCOL.object_id = OBJECT_ID( ISCOL.TABLE_NAME )
AND SYSIDCOL.[name] = ISCOL.COLUMN_NAME
LEFT OUTER JOIN
(
SELECT DISTINCT
SYSINDX.object_id AS [ObjectID]
, SYSINDX.column_id AS [ColumnID]
FROM sys.index_columns AS SYSINDX (NOLOCK)
) AS INDX ON INDX.[ObjectID] = SYSCOL.object_id
AND INDX.[ColumnID] = SYSCOL.column_id
ORDER BY
ISCOL.TABLE_NAME
, ISCOL.ORDINAL_POSITION;
0b8ad849-7edb-41e7-90e4-7a5e4ec3badd|0|.0