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

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'

Answer 2

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

exec sp_columns TableName

You can also use sp_help.

Answer 3

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

Answer 4

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]:

Answer 5

May be this can help:

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

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

Answer 6

I like the answer that attempts to do the translate, however, while using the code it doesn't like columns that are not VARCHAR type such as BIGINT or DATETIME. I needed something similar today so I took the time to modify it more to my liking. It is also now encapsulated in a function which is the closest thing I could find to just typing describe as oracle handles it. I may still be missing a few data types in my case statement but this works for everything I tried it on. It also orders by ordinal position. this could be expanded on to include primary key columns easily as well.

CREATE FUNCTION dbo.describe (@TABLENAME varchar(50))
returns table
as
RETURN
(
SELECT TOP 1000 column_name AS [ColumnName],
       IS_NULLABLE AS [IsNullable],
       DATA_TYPE + '(' + CASE 
                                    WHEN DATA_TYPE = 'varchar' or DATA_TYPE = 'char' THEN 
                                      CASE 
                                        WHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = -1 THEN 'Max'
                                        ELSE Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
                                      END
                                    WHEN DATA_TYPE = 'decimal' or DATA_TYPE = 'numeric' THEN
                                      Cast(NUMERIC_PRECISION AS VARCHAR(5))+', '+Cast(NUMERIC_SCALE AS VARCHAR(5))
                                    WHEN DATA_TYPE = 'bigint' or DATA_TYPE = 'int' THEN
                                      Cast(NUMERIC_PRECISION AS VARCHAR(5))
                                    ELSE ''
                                  END + ')' AS [DataType]
FROM   INFORMATION_SCHEMA.Columns
WHERE  table_name = @TABLENAME
order by ordinal_Position
);
GO

once you create the function here is a sample table that I used

create table dbo.yourtable
(columna bigint,
 columnb int,
 columnc datetime,
 columnd varchar(100),
 columne char(10),
 columnf bit,
 columng numeric(10,2),
 columnh decimal(10,2)
 )

Then you can execute it as follows

select * from describe ('yourtable')

It returns the following

ColumnName IsNullable DataType

---

columna NO bigint(19)
columnb NO int(10)
columnc NO datetime()
columnd NO varchar(100)
columne NO char(10)
columnf NO bit()
columng NO numeric(10, 2)
columnh NO decimal(10, 2)

hope this helps someone.

Answer 7

As a variation of Bridge's answer (I don't yet have enough rep to comment, and didn't feel right about editing that answer), here is a version that works better for me.

SELECT column_name AS [Name],
   IS_NULLABLE AS [Null?],
   DATA_TYPE + CASE
                 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
                 WHEN CHARACTER_MAXIMUM_LENGTH > 99999 THEN ''
                 ELSE '(' + Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')' 
               END AS [Type]
FROM   INFORMATION_SCHEMA.Columns
WHERE  table_name = 'table_name'

Notable changes:

• Works for types without length. For an int column, I was seeing NULL for the type because the length was null and it wiped out the whole Type column. So don't print any length component (or parens).
• Change the check for CAST length of -1 to check actual length. I was getting a syntax error because the case resulted in '*' rather than -1. Seems to make more sense to perform an arithmetic check rather than an overflow from the CAST.
• Don't print length when very long (arbitrarily > 5 digits).

Answer 8

Just enter the below line.

exec sp_help [table_name]