Get all fields names for all tables in SQL

  • halifaxdal

    SSCoach

    Points: 19584

    Comments posted to this topic are about the item Get all fields names for all tables in SQL

  • Muzzzza

    SSC Enthusiast

    Points: 158

    I use this script to extract all fields from all tables.

    select so.[Name] 'Table', sc.ColID 'Seq', sc.[Name] 'Field'

    FROM syscolumns sc

    INNER JOIN sysobjects so ON so.[ID] = sc.[ID]

    WHERE so.xtype in (

    'IT' /*Internal table*/

    ,'U' /*User table */

    ,'V' /*View */

    )

    ORDER BY so.[Name], sc.ColID, sc.[Name]

    I use it to populate an Excel spreadsheet where I put filters on 'Table' and 'Field'.

    Filtering on a field name shows all tables with that field

    Filtering using 'contains' also shows similarly named fields e.g. all fields containing 'Route'

    I can also list all fields in a specific table without going to SSMS.

  • whenriksen

    SSCarpal Tunnel

    Points: 4742

    For SQL 2005+, use the built in information schema views.

    SELECT TABLE_CATALOG

    , TABLE_SCHEMA

    , TABLE_NAME

    , COLUMN_NAME

    , ORDINAL_POSITION

    , COLUMN_DEFAULT

    , IS_NULLABLE

    , DATA_TYPE

    , CHARACTER_MAXIMUM_LENGTH

    , CHARACTER_OCTET_LENGTH

    , NUMERIC_PRECISION

    , NUMERIC_PRECISION_RADIX

    , NUMERIC_SCALE

    , DATETIME_PRECISION

    , CHARACTER_SET_CATALOG

    , CHARACTER_SET_SCHEMA

    , CHARACTER_SET_NAME

    , COLLATION_CATALOG

    , COLLATION_SCHEMA

    , COLLATION_NAME

    , DOMAIN_CATALOG

    , DOMAIN_SCHEMA

    , DOMAIN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    Wes
    (A solid design is always preferable to a creative workaround)

  • HextallFanForLife

    SSC Veteran

    Points: 216

    You can do the same thing with sp_help 'tablename'

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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