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

Use a query to document your SQL Server database

Here's how you can use a single query to document your database in Microsoft SQL Server.

Did you know that you can use a query to document your SQL Server 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 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 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 '?'"

Brian F. writes: I prefer the tip from Kenneth Moser for using information_Schema.columns better than Parthasarathy Mandayam's tip. It also gives more information such as nulls and precision.

 

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.


Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close