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.

You can use sp_columns, a system stored procedure for describing a table.

exec sp_columns TableName

You can also use sp_help.

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'

The sp_help built-in procedure is the SQL Server's closest thing to Oracle's DESC function IMHO

sp_help MyTable

Use

sp_help "[SchemaName].[TableName]" 

or

sp_help "[InstanceName].[SchemaName].[TableName]"

in case you need to qualify the table name further

You can use keyboard short-cut for Description/ detailed information of Table in SQL Server 2008.

Follow steps:

1. Write Table Name,
2. Select it, and press Alt + F1

3. It will show detailed information/ description of mentioned table as,

   1) Table created date,
   2) Columns Description,
   3) Identity,
   4) Indexes,
   5) Constraints,
   6) References etc. As shown Below [example]:

May be this can help:

Use MyTest
Go
select * from information_schema.COLUMNS where TABLE_NAME='employee'

{ where: MyTest= DatabaseName Employee= TableName } --Optional conditions