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,0AS Length,
    
COALESCE(numeric_scale,0AS 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