May 5, 2004 at 12:05 pm
I've been looking for a system stored procedure that would show me table information so I dont have to write it out myself (I'm writing up doco - ugh). The more info the better as it needs to be detailed (column names, datatypes etc...) can anyone help?? also is there some 'magical' list of system stored procedures as I can only find a few of them on google and MS doesnt seem to want users to know them (thanks MS - that's helpful)
May 5, 2004 at 1:44 pm
you can query the information_schema views in the current database to get your column names and data types:
see below for example on tempdb database:
use tempdb
select * from information_schema.tables
select * from information_schema.columns
May 5, 2004 at 1:49 pm
and of couse there is also the sp_help stored procedure that will also get you the column name and data type information for a given table.
May 5, 2004 at 4:43 pm
As to the magic list -- If you open SQL Query Analyzer, (From Enterprise Manager Select a Database, Click the Tools Menu...Choose SQL Query Analyzer) And choose Transact SQL Help From the Help Menu in Query Analyzer (and this will depend on your installation of SQL Server) But this will typically open the SQL Server Books Online, which i pretty comprehensive. Use the Index search and type in "sp_" and your magic list will appear. Note, there are several undocuments system stored procedures as well that you will not find here.
May 5, 2004 at 9:53 pm
All of the methods about system tables and procedures that begin with "sp_" are great... but if all you want to do is get all the columnar info to show up on screen including the column name, datatype, NULLability, etc, then there's no need to reinvent the wheel...
1. Open Query Analyzer in the database of your choice.
2. Press the [F8] key to show the "Object Browser"
3. Open the [User Tables] folder
4. Right-click on the table of your choice and a new pop-up window will appear.
5. Among the choices, you will find options to script the table to a new window, a file, the clipboard, etc (including [Scripting Options] which you should take the time to setup as you like). Open any of those and you find [CREATE] to be one of the choices which is really good for documenting table structure as you want.
6. I can't stress enough to setup the scripting options where you can turn of such nuisances as COLLATION ORDER, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2004 at 9:32 am
Thanks all for the help, this will give me a few ways to look at the issue. I still find it funny that MS doesnt publish the System Procedures (I've been able to find a few on google) as I think they would come in handy for a DBA - wierd.
-Matt
May 6, 2004 at 1:33 pm
Matt,
If you open SQL Server Books Online (IE: Sql servers version of help) and put "system stored procedures, listed" in the Index you will get all the publically listed system stored procedures.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply