Post reply

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

  • 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)

  • May 5, 2004 at 1:44 pm

    #505364

    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

    #505365

    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

    #505382

    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

    #505398

    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)
    Intro to Tally Tables and Functions

  • May 6, 2004 at 9:32 am

    #505502

    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

    #505547

    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