What is DESCRIBE command in SQL Server?

What is its purpose?How to use it?

Editorial / Best Answer

suji  

  • Member Since Sep-2005 | Nov 5th, 2011


Here are multiple ways to get the table information. The DESCRIBE command does not exist in MS SQL SERVER. This is an Oracle command used to describe the structure of objects within a given database. To achieve the same task in MSSQL Server, there are a series of stored procedures with the prefix SP_ that can be used. To view the structure of a table within the current database, use the command

Code
  1. sp_help 'TABLE_NAME';
If you would like to see more details, Create your custom procedure
Code
  1. CREATE procedure DESCRIBE
  2. (
  3. @tablename varchar(256)
  4. )
  5. AS
  6. begin
  7. SELECT DISTINCT sCols.colid AS 'order', sCols.name, sTyps.name, sCols.length
  8. FROM [syscolumns] sCols
  9. INNER JOIN [systypes] sTyps ON sCols.xtype = sTyps.xtype
  10. INNER JOIN [sysobjects] sObjs ON sObjs.id = sCols.[id]
  11. AND UPPER(sObjs.name) = UPPER(@tablename)
  12. ORDER BY sCols.colid
  13. end
Here is another alternative way to get the same information
Code
  1. sp_columns 'TableName' (e.g. sp_columns 'Employee')
  2. sp_columns [ @table_name = ] object [ , [ @table_owner = ] owner ]
  3. [ , [ @table_qualifier = ] qualifier ]
  4. [ , [ @column_name = ] COLUMN ]
  5. [ , [ @ODBCVer = ] ODBCVer ]
Here is the usage :
Code
  1. EXEC sp_columns @table_name = 'Department', @table_owner = 'sa';
  2. SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName'
Contributors for the editorial answer : Kewlshiva, srilakshmi.b, raaghav,kevaburg

Showing Answers 1 - 20 of 20 Answers

acp3012

  • Sep 17th, 2006
 

DESCRIBE is not a SQL Server Command. This a command used in ORACLE & MYSQL to display the sturcture of a Table.

  Was this answer useful?  Yes

Lehman

  • Sep 19th, 2006
 

What is the command in SQL Server to display the structure of the table?

  Was this answer useful?  Yes

Anu

  • Sep 20th, 2006
 

Suppose if u want to get the structure of table categories

use the following command in sql server

sp_columns categories

This will work.. Like wise if u want to see the list of tables in ur database, use

sp_tables

 

Regards,

Anu

 

Rajneesh Kaur

  • Dec 13th, 2006
 

DESCRIBE command is used to view the structure of a table..

Eg. a table named employee and we want to see the structure of a table then we will use command as:

DESC employee

This will display whole structure of employee table.

  Was this answer useful?  Yes

Kewlshiva

  • Mar 26th, 2008
 

you can get the same type of results in SQL SERVER also using the following Stored Proc

CREATE  procedure describe
(
 @tablename varchar(256)
)
as
begin
SELECT distinct sCols.colid as 'order', sCols.name, sTyps.name, sCols.length
FROM [syscolumns] sCols
 INNER JOIN [systypes] sTyps ON sCols.xtype = sTyps.xtype
 INNER JOIN [sysobjects] sObjs ON sObjs.id = sCols.[id]
  and UPPER(sObjs.name) = UPPER(@tablename)
order by sCols.colid
end

raaghav

  • Dec 9th, 2010
 

Use the Following Code to Get the Structure or Description of the Table in SQL Server. 


sp_columns 'TableName' (e.g. sp_columns 'Employee') 


or 


SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableName'
(e.g.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employee'
)


  Was this answer useful?  Yes

kevaburg

  • May 1st, 2011
 

The DESCRIBE command does not exist in MS SQL SERVER.  This is an Oracle command used to describe the structure of objects within a given database.

To achieve the same task in MSSQL Server, there are a series of stored procedures with the prefix SP_ that can be used.  In your case, to view the structure of a table within the current database, use the command sp_help 'TABLE_NAME';

Hope this helps!

  Was this answer useful?  Yes

Here are multiple ways to get the table information.
The DESCRIBE command does not exist in MS SQL SERVER. This is an Oracle command used to describe the structure of objects within a given database. To achieve the same task in MSSQL Server, there are a series of stored procedures with the prefix SP_ that can be used.
To view the structure of a table within the current database,
use the command

Code
  1. sp_help 'TABLE_NAME';

If you would like to see more details, Create your custom procedure
Code
  1. CREATE procedure DESCRIBE

  2. (

  3. @tablename varchar(256)

  4. )

  5. AS

  6. begin

  7. SELECT DISTINCT sCols.colid AS 'order', sCols.name, sTyps.name, sCols.length

  8. FROM [syscolumns] sCols

  9. INNER JOIN [systypes] sTyps ON sCols.xtype = sTyps.xtype

  10. INNER JOIN [sysobjects] sObjs ON sObjs.id = sCols.[id]

  11. AND UPPER(sObjs.name) = UPPER(@tablename)

  12. ORDER BY sCols.colid

  13. end


Here is another alternative way to get the same information
Code
  1. sp_columns 'TableName' (e.g. sp_columns 'Employee')

  2. sp_columns [ @table_name = ] object [ , [ @table_owner = ] owner ]

  3. [ , [ @table_qualifier = ] qualifier ]

  4. [ , [ @column_name = ] COLUMN ]

  5. [ , [ @ODBCVer = ] ODBCVer ]

Here is the usage :
Code
  1. EXEC sp_columns @table_name = 'Department', @table_owner = 'sa';

  2. SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName'

Contributors for the editorial answer : Kewlshiva, srilakshmi.b, raaghav,kevaburg

  Was this answer useful?  Yes

Nirav

  • Dec 29th, 2011
 

This will give you the best information of your table in SQL Server. Like Name of Table, Column Name, Identity, Row Guid Col, Data Located On Filegroup, Index Name and Constraint Type.
EXECUTE sp_help

Code
  1. EXECUTE sp_help <tableName>

  Was this answer useful?  Yes

gangireddy

  • Feb 22nd, 2012
 

To see the structure (description ) of the table in Sql Server the followed command surely works....
sp_help person

Note:***
to display the structure of the table in Sql Server wer use the followed command ...it has the accurate rating ****
sp_help
sp_help person

  Was this answer useful?  Yes

Saket Kale

  • Oct 24th, 2012
 

sp_help dbo.customers;
worked for me just fine.
Thanks to the OP, this runs perfectly on SQL 2008 R2, do not know about other versions

  Was this answer useful?  Yes

Rooshi

  • Oct 17th, 2014
 

sp_help will give all the details about the table columns, indexes, partitions etc.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions