Problem solve Get help with specific problems with your technologies, process and projects.

How to use a SQL query to document your database

Here's how to use a single SQL query to document your SQL Server database.

Did you know that you can use a SQL query to document your SQL Server 2000 database? This simple query on the system tables will do just that. You can easily cut and paste the results into a Word document and improve the look and feel. Imagine the amount of typing you will save! I used this SQL query to document a 100 table database in a couple of seconds.

SELECT
table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype=systypes.xtype
where sysobjects.xtype='U'
order by sysobjects.name,syscolumns.colid
The code has been tested on SQL Server 2000 and should work even on SQL 7.0.

Reader Feedback

Kelly B. writes: Companies that have their own data types defined may cause multiple uses of a single xtype in the systypes table, thus causing too many rows returned (i.e., more than one row per column in a table) However if you use the xusertype in the SQL query you may come up with a more accurate answer.

SELECT
table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype=systypes.xtype
where sysobjects.xtype='U'
order by sysobjects.name,syscolumns.colid

I came up with 147675 rows.

SELECT
table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype=systypes.xusertype 
where sysobjects.xtype='U'
order by sysobjects.name,syscolumns.colid

I came up with the appropriate 7080 rows.

Robert H. writes: What's the point? To script tables and/or indexes, logins, users, permissions, etc. in MS SQL 7.0 or MS SQL 2000 (and as far back as v. 6.5 I think), simply right-click on a database, select 'All Tasks', and select 'General SQL Script'.

Lowell S. writes: Try this one-liner:

exec sp_MSForeachTable @command1 = "sp_help '?'"


Dig Deeper on SQL Server and .NET development

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

TheServerSide.com

Close