Codebureau - Matt Simner - Data Dictionary from within SQL Server

Wednesday, March 26, 2008

Data Dictionary from within SQL Server

I knew you could do this, but never took the time to find out exactly which sys... table the comments got stored in. sysProperties is what you want, and this article on SQLServerCentral tells you how...

The easiest way to get them 'in' is in Enterprise Manager, and someone posted a slightly amended version of the code to get them out (I also added IsNullable)...

DECLARE @TableName VARCHAR(128)

SET @TableName = 'Catalogue'

SELECT
    T.table_name AS TableName,
    C.ordinal_position AS ColumnOrder,
    C.[column_name] AS ColumnName,
    ISNULL(P.value, '') AS ColumnDescription,
    C.DATA_TYPE AS DataType,
    COALESCE(numeric_precision,character_maximum_length,0) AS Length,
    COALESCE(numeric_scale,0) AS DecimalPos,
    C.is_nullable AS IsNullable,
    ISNULL(C.COLUMN_DEFAULT, '') AS DefaultValue
    FROM information_schema.tables T
        INNER JOIN information_schema.columns C
            ON T.table_name = C.table_name
        LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @TableName, N'column', null) P
            ON C.column_name = P.objname
    WHERE T.table_name = @TableName

Colorized by: CarlosAg.CodeColorizer

SQL Server

posted on Wednesday, March 26, 2008 8:46:43 AM (AUS Eastern Daylight Time, UTC+11:00)

Comments are closed.