SQL Wiki - Automatic documentation stubb generation

Post date: May 31, 2011 7:41: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