Documenting Tables and Queries

Hi,
I am completely new to SQL Server, just installed. I had been using
MSDE2000 on the way to re-writing a MS Access 2000 app into a Visual
Basic.Net web app.
I have learned to use SQL Server DTS to move the MS Access data to my new
SQL Server database. I am learning about SQL Server Enterprise Manager and
using it successfully.
In MS Access 2000, there is a simple menu item that allows you to document
the tables and queries, getting a hardcopy printout of the table properties.
I have not been able to find a similar capability within SQL Server. It
does not seem to have much hard copy print capability at all. I understand
that you can display everything, maybe I am just old fashion in wanting some
hard copy printout of the database table structures and properties.
Also, could you recommend a way of familiarizing myself with the functions
and capabilities (with examples) of this SQL Query Analyzer. It looks
really powerful. I believe that I just need to get initiated into the
concept and the syntax or language structure.
Thanks,
hugh
Hi Hugh,
Thanks for your post.
From your descriptions, I understood you would like to know how to document
the tables and queries in SQL Server. However, I would like to restate my
understanding of "document". You want to get the struct of the tables, the
codes of stored procedures, etc. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, SQL Server does not provide a stored procedure
directly.
If you want the code of a stored procedure, you could get the script from
SQL Server Enterprise Manager
- Click the database object
- All Task -> Generate SQL Task...
If you want to get the structure of tables, you could search the people
customized SELECT statements and some third party tools. Generally
speaking, I will use the query below for your reference
SELECT
'Table Name'=case when a.colorder=1 then d.name else '' end,
'ColName'=a.name,
'Identity'=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '
'else '' end,
'PK'=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name
in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '' else '' end,
'Type'=b.name,
'Length'=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
'Allow Null'=case when a.isnullable=1 then ''else '' end,
'Default Value'=isnull(e.text,'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and
d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:6AULFbFqFHA.2928@.TK2MSFTNGXA01.phx.gbl...
> Hi Hugh,
> Thanks for your post.
> From your descriptions, I understood you would like to know how to
> document
> the tables and queries in SQL Server. However, I would like to restate my
> understanding of "document". You want to get the struct of the tables, the
> codes of stored procedures, etc. If I have misunderstood your concern,
> please feel free to point it out.
> Based on my knowledge, SQL Server does not provide a stored procedure
> directly.
You guys should create a Data Dictionary segment for SQL Server and hide it
under Managment. DB2 has had this capapbility for a long time.
The drawing of a model in SS2000 is pretty lame.
|||Hugh:
I applaud your honesty in admitting that you are new to SQL and its
capabilities. The more I learn, the more I realize I don't know!
There are some good third-party tools for documenting databases. Check out
fmsinc.com. They developed a tool for documenting MS Access databases (which
actually do a decent job on Access Data Projects) and another higher priced
one for SQL Server.
Good luck.
TOdd