I've started to document the project formerly known as ASP.NET Forums and now known as Community Server :: Forums. In the process of documenting the product I need to add the database schema to the Wiki but being a lazy programmer I'm always looking for more efficient ways to get my work done.
Since I've been working with SQL Server for about 10 years now if I need anything out of the database and I have to generate reports or some kind of tabular data I alwasy try to find a way I can get the information I need from the database and let the database produce the report the way I need it.
So in the Forums Wiki I wanted to generate a heading of Database Schema with each table listed as a link and then when I got to each link I wanted to print each table name again and then the columns in a table with the column name, datatype, key information, nullability and a general comment about the column.
So I wrote a quick little TSQL script to generate the information for me in the proper Wiki format.
select
'!!!Database Schema'
, '1' as [dorder]
union all
select
'[' + o.name + ']'
, '2.' + o.name + '.0'
from sysobjects o where o.type = 'U' and o.name like 'forums%'
union all
select
'!!!!' + o.name
, '2.' + o.name + '.1' [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 + '.2' [dorder]
from sysobjects o where o.type = 'U' and o.name like 'forums%'
union all
select '||[' + c.name + ']||'
+ type_name(c.xusertype) + case c.xusertype
when 62 then '(' + convert(varchar(10), xprec) + ','
+ convert(varchar(10), xscale) + ')'
when 99 then '(' + convert(varchar(10), length) + ')'
when 108 then '(' + convert(varchar(10), xprec) + ','
+ convert(varchar(10), xscale) + ')'
when 165 then '(' + convert(varchar(10), length) + ')'
when 167 then '(' + convert(varchar(10), length) + ')'
when 173 then '(' + convert(varchar(10), length) + ')'
when 175 then '(' + convert(varchar(10), length) + ')'
when 231 then '(' + convert(varchar(10), length) + ')'
else ''
end
+ '||' + 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
Now to generate a Data Dictionary I used the following script.
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
Now that the Database Schema and Data Dictionary are created with dummy links I just need to go to each one and create the page and copy in the information that is supposed to be on each page.