system SP - table data needed (column names & datatypes)

Question

Post reply

system SP - table data needed (column names & datatypes)

Matt Miskinnis

SSC-Addicted

Points: 461
More actions
May 5, 2004 at 12:05 pm

#108238

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)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

bp SSCarpal Tunnel Points: 4656 More actions · Answer 1

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

bp SSCarpal Tunnel Points: 4656 More actions · Answer 2

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.

Peter P SSCrazy Points: 2777 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miskinnis SSC-Addicted Points: 461 More actions · Answer 5

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

gljjr SSChampion Points: 10080 More actions · Answer 6

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.