SQL: List of table columns, with data types

The Transact-SQL query below 1, returns the table name, column name, data type, and maximum length. This is useful when producing database documentation

  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

2 Comments

Add a Comment
  1. The code above works with SQL Server 2005. However, for SQL Server 2000, you will need to use the code below:

    SELECT
    O.name AS TableName,
    C.name AS ColumnName,
    T.name AS Type,
    C.length AS MaxLength
    FROM sysobjects O
    INNER JOIN syscolumns C
    ON O.id = C.id
    INNER JOIN systypes T
    ON C.xtype = T.xtype
    WHERE O.type = ‘U’
    ORDER BY
    O.name,
    C.colid

  2. Also, the query above is joined on the system type. Just replace:

    INNER JOIN sys.types T
    ON C.system_type_id = T.system_type_id

    with

    INNER JOIN sys.types T
    ON C.user_type_id = T.user_type_id

    to return the user type.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

CJayMead's SQL Server, Endeca, MS Office Help © 2018