Post reply

List of columns in a database

  • March 4, 2008 at 6:56 am

    #120677

    Hi All.

    I'm an Oracle DBA who's currently being asked to look at a SqlServer Database. I need a list of columns per table, but am having trouble.

    I'll admit I might be being lazy here, but I'm in a hurry and using the valueable resources available to me!! Would really appreciate the sql i need to copy into the query window. Much obliged!!

    I need........

    Table A

    Column1 Datatype

    Column2 Datatype

    Table B

    Column1 Datatype

    Column2 Datatype

    etc....

    Many thanks.

  • March 4, 2008 at 7:05 am

    #784920

    use this

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS

    you will need to develop further.

    Pedro R. Lopez
    http://madurosfritos.blogspot.com/[/url]

  • March 4, 2008 at 7:13 am

    #784925

    Try this

    select table_name,column_name,data_type from information_schema.columns where table_name not in (

    select name from sysobjects where type='v') order by table_name

  • March 4, 2008 at 8:05 am

    #784973

    Thanks Maverik

  • March 4, 2008 at 9:16 am

    #785015

    I dont know about you, but I would want as much information as possible. I would display a few more columns.

    SELECT

    c.Table_Name,

    c.Column_Name,

    c.Data_Type,

    c.character_maximum_length,

    c.Numeric_Precision,

    c.Numeric_scale,

    c.Is_Nullable,

    c.Column_Default,

    c.Collation_Name

    FROM INFORMATION_SCHEMA.COLUMNS c

    INNER JOIN SYS.TABLES so

    ON c.Table_Name = so.Name AND so.Type = 'U'


    My blog: http://jahaines.blogspot.com

  • March 4, 2008 at 9:54 am

    #785037

    I gotta agree with that...

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

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

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