Post reply

Display Table name and columns

  • June 7, 2004 at 3:36 pm

    #108081

    Hello,  Is it possible to use the system tables to create a sort of data dictionary? I'd like to report the table name, column name, and data type.

    Thanks, Steve DiDomenico, Nashua, NH

  • June 7, 2004 at 4:32 pm

    #509354

    Take a look at INFORMATION_SCHEMA in books online.

    Steve

  • June 7, 2004 at 4:33 pm

    #509355

    Look at

    information_schema.columns

    and

    information_schema.tables

     

    also what is your goal as it might work better with SQL-DMO?

  • June 8, 2004 at 12:44 am

    #509386

    Here is a little script I use.

    HTH

    Leon

    DECLARE @TABELNAME nVarchar(500)

    SET @TABELNAME = 'PG_DATA_SALES'

    SELECT

     column_name = object_columns.[name],

     value_type = object_column_type.[name] + '(' + CAST(object_columns.[length] AS varchar(5)) + ')',

     value_precision = object_columns.[xprec],

     value_scale = object_columns.[xscale],

     value_nullable =

     CASE object_columns.[isnullable]

      WHEN 0 THEN 'FALSE'

      ELSE 'TRUE'

     END,

     column_position = object_columns.[colorder]

    FROM sysobjects objects

    INNER JOIN syscolumns object_columns

     ON objects.[id] = object_columns.[id]

    INNER JOIN systypes object_column_type

     ON object_columns.[xtype] = object_column_type.[xtype]

    WHERE objects.[name] = @TABELNAME

    ORDER BY object_columns.[name]

    Regards,
    Leon Bakkers

  • June 8, 2004 at 12:50 am

    #509387

    Hi!

    try this script, probably solve your purpose....

    select table_name, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, isnull( convert(varchar, CHARACTER_MAXIMUM_LENGTH), '-NA-') as CHARACTER_MAXIMUM_LENGTH 

    from information_schema.columns

    order by table_name

    Best Regards,

    Indu Jakhar

  • June 8, 2004 at 2:33 am

    #509404

    You can also use the System Stored procedures sp_tables and sp_columns

    Look them up in Books Online Catalogue functions

  • June 8, 2004 at 3:02 am

    #509408

    You can also press Alt+F1 while the tablename is highlighted in QA.

  • June 8, 2004 at 9:27 am

    #509506

    Just a quick note.  You said, "to create a sort of data dictionary."

    There's not question about it, the system tables ARE the data dictionary.  There may be views and tools prewritten to help you get to the information but a through understanding of all the systems tables is essential to any DBA.

    Don't just review the system tables in a database either.  Look at the system tables in the MASTER database too.  Here is where you see information about each of the databases also.

    Then look at sp_xxxx also.

    Always a student...

  • June 8, 2004 at 12:12 pm

    #509541

    Thanks for all of the replys. Leon, your script is what I was looking for.  Thanks a million.

  • June 10, 2004 at 9:02 am

    #509895

    This will return all user tables and columns in a db. Its helpful when your searching for spicific for cols.

    Select o.name, c.name
    From sysobjects o
    Join syscolumns c on o.id = c.id
    Where o.xtype = 'U'
    and o.status > 0
    -- Narrow down search
    --and c.name like '%log%'
    Order by 1,2

    Thank you.
    Francis S. Mazeika
    MS SQL DBA
    609-707-5207
    francis.mazeika@gmail.com

Viewing 10 posts - 1 through 9 (of 9 total)

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