Just Code‎ > ‎

SQL Wiki - Automatic documentation stubb generation

posted May 31, 2011, 12:41 AM by Peter Henell   [ updated May 31, 2011, 3:58 AM ]
This will generate a Wikitable with a header for each table in the database, comments you have to write for yourself!
If you add your comments as extended properties with "Description" as the key, then they will show up in this automaticaly
SELECT 
WikiMarkup FROM 
INFORMATION_SCHEMA.tables iss
CROSS APPLY(
    SELECT 'h2. ' + iss.TABLE_SCHEMA + '.' + iss.TABLE_NAME + ' Table' WikiMarkup UNION ALL
    
    SELECT ISNULL( 
                ( SELECT CAST(VALUE AS NVARCHAR(MAX)) FROM fn_listextendedProperty('Description', 
                 'SCHEMA', iss.TABLE_SCHEMA
                 ,'TABLE', iss.TABLE_NAME
                 , NULL, NULL))
         , 'comment missing') + ' \\' UNION ALL
         
    SELECT '||COLUMN_NAME|' +  '|IS_NULLABLE|' +  '|DATA_TYPE|' +  '|CHARACTER_MAXIMUM_LENGTH|' +  '|COLUMN_DEFAULT|' + '|Comment|' UNION ALL
    
    SELECT '|' + ISNULL(COLUMN_NAME,'') + '|' + ISNULL(IS_NULLABLE,'') + '|' + ISNULL(DATA_TYPE, '') + '|' + ISNULL(CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)), 'N/A')  + '|' + ISNULL(COLUMN_DEFAULT, 'none') + '|' + 
            ISNULL(
                (SELECT CAST(VALUE AS NVARCHAR(MAX)) FROM fn_listextendedProperty('Description', 
                 'SCHEMA', iss.TABLE_SCHEMA
                 ,'TABLE', iss.TABLE_NAME
                 , 'COLUMN', Column_Name))
            , 'comment missing') +' |'

     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = iss.TABLE_NAME
 ) AllTheGoodies
 
 --WHERE iss.TABLE_SCHEMA <> 'dbo'
 ORDER BY iss.TABLE_NAME
Comments