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

SELECT 
      schema_name(O.schema_id) + '.' + O.name AS TableName, 
      C.name AS ColumnName, 
      T.name AS Type, 
      C.max_length AS Length
FROM        sys.objects O
INNER JOIN  sys.columns C
      ON O.object_id = C.object_id
INNER JOIN  sys.types T
      ON C.system_type_id = T.system_type_id
WHERE O.type = 'U'
ORDER BY
      schema_name(O.schema_id), 
      O.name, 
      C.column_id
  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 e-mail address will not be published. Required fields are marked *

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