How to describe table in SQL Server 2008?

I want to describe a table in SQL Server 2008 like what we can do with the DESC command in Oracle.

I have table [EX].[dbo].[EMP_MAST] which I want to describe, but it does not work.

Error shown:

The object ‘EMP_MAST’ does not exist in database ‘master’ or is invalid for this operation.

Answer

According to this documentation:

DESC MY_TABLE

is equivalent to

SELECT column_name “Name”, nullable “Null?”, concat(concat(concat(data_type,'(‘),data_length),’)’) “Type” FROM user_tab_columns WHERE table_name=’TABLE_NAME_TO_DESCRIBE’;

I’ve roughly translated that to the SQL Server equivalent for you – just make sure you’re running it on the EX database.

SELECT column_name AS [name],
       IS_NULLABLE AS [null?],
       DATA_TYPE + COALESCE('(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1
                                  THEN 'Max'
                                  ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
                                  END + ')', '') AS [type]
FROM   INFORMATION_SCHEMA.Columns
WHERE  table_name = 'EMP_MAST'