How to describe table in SQL Server 2008?

Question

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 1

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

exec sp_columns TableName 

You can also use sp_help.

Answer 2

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'