This post originated from an RSS feed registered with .NET Buzz by Jason Alexander. Original Post: Database Schema to Wiki Trick Feed Title: Jason Alexander's Blog Feed URL: http://www.asp.net/err404.htm?aspxerrorpath=/jalexander/Rss.aspx Feed Description: Tales from the .NET trenches... Latest .NET Buzz Posts Latest .NET Buzz Posts by Jason Alexander Latest Posts From Jason Alexander's Blog

Ok, this is a really slick trick for generating wiki mark-up for the database schema in SQL Server. One of my buddies, Terry Denham from the Community Server :: Forums team (previously the ASP.NET Forums) and co-worker here at Telligent, came up with this nice little script:

select '!!!Database Schema', '1' as [dorder]
union all
select '!!!!' + o.name, '2.' + o.name + '.0' [dorder]
         from sysobjects o where o.type = 'U' and o.name like 'forums%'
union all
select         '||''''''Column Name''''''||''''''Data Type''''''||''''''Nullable''''''||''''''Key''''''||''''''Description''''''||', '2.' + o.name + '.1' [dorder]
         from sysobjects o where o.type = 'U' and o.name like 'forums%'
union all
select        '||[' + c.name + ']||' + type_name(c.xusertype) + '(' + convert(varchar(20), convert(int, c.length)) + ')' + '||' + case when c.isnullable = 0  then 'no' else 'yes' end + '||||||'
          , '2.' + o.name + '.2.' + convert(varchar(20), c.colid) [dorder]
         from sysobjects o inner join syscolumns c on o.id = c.id where o.type = 'U' and o.name like 'forums%'
order by 2, 1

Just paste the output into your favorite wiki (this has been tested with FlexWiki) and, voila, you have your schema in a nice readable wiki format!

Also, you can run the following SQL script to generate the corresponding data dictionary:

select '!!!Data Dictionary', '1' as [dorder]

union all

select distinct '[' + c.name + ']', '2.' + c.name from sysobjects o inner join syscolumns c on o.id = c.id where o.name like 'forums%' and o.type = 'U'

order by 2, 1

This works great when you have pascal cased columns because your columns will become wiki links, and you can then use the latter script to gen the corresponding data dictionary.

Very nice!

(NOTE: Apologies on the poor formatting. Somehow this editor has mangled my poor post!)