MsSql lacks describe statement

Describe, or desc in short, is one of the most valuable commands when trying to understand a database structure. MsSql lacks this feature so you have to write it yourself.

SELECT
    column_name
,   data_type +
    COALESCE(
       '(' + CAST(character_maximum_length AS VARCHAR) + ')'
    ,  '(' + CAST(numeric_precision AS VARCHAR) + ')'
    ,  ''
    )
,   column_default
,   is_nullable
FROM
    information_schema.columns
WHERE
    table_name = 'tablename';

Also see Getting a table description.

8 Responses to “MsSql lacks describe statement”

  1. @acn: You probably mean

    exec sp_columns tablename
    

    or even better

    exec sp_columns tablename, @column_name = 'foo'
    

    This indeed works.

  2. acn says:

    sp_columns tablename

    is equivalent command for DESC from Oracle.

    MS-sql doesnot lack desc

  3. Renith Ravindran says:

    Nice one… Still we can modify this to short as follows…

    select column_name,data_type from information_schema.columns where table_name = ‘your table name’;

  4. Ramesh Marikhu says:

    Nice one. Did not know MSSQL lacked DESCRIBE capability. Thanks.

  5. BoĊĦtjan says:

    Nice solution! Thanks

  6. Thanks for posting this. You just saved me hours of banging my head against a pesky MSSQL db.

  7. S Halayka says:

    Thank you, this is great.

  8. Duane Zamrok says:

    Thank you very much. It’s is very refreshing to find a result that is upfront and straight to the point for once.

    -Duane