GeekInterview.com
Series: Subject: Topic:
Question: 152 of 198

What is DESCRIBE command in SQL Server?

What is its purpose?How to use it?
Asked by: Interview Candidate | Asked on: Aug 30th, 2006

Editorial / Best Answer

Answered by: suji

View all questions by suji   View all answers by suji

Member Since Sep-2005 | Answered On : 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

Answered On : Sep 17th, 2006

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

  
Login to rate this answer.
Lehman

Answered On : Sep 19th, 2006

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

  
Login to rate this answer.
Anu

Answered On : Sep 20th, 2006

Suppose if u want to get the structure of table categoriesuse the following command in sql serversp_columns categoriesThis will work.. Like wise if u want to see the list of tables in ur database, usesp_tables Regards,Anu 

Yes  2 Users have rated as useful.
  
Login to rate this answer.
opbang

Answered On : Sep 21st, 2006

View all answers by opbang

Use sp_help to display table structure in Query Analyzer

Yes  1 User has rated as useful.
  
Login to rate this answer.
Rajneesh Kaur

Answered On : 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 employeeThis will display whole structure of employee table.

  
Login to rate this answer.
Kewlshiva

Answered On : Mar 26th, 2008

View all answers by Kewlshiva

you can get the same type of results in SQL SERVER also using the following Stored ProcCREATE  procedure describe ( @tablename varchar(256))as beginSELECT distinct sCols.colid as 'order', sCols.name, sTyps.name, sCols.lengthFROM [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.colidend

Yes  1 User has rated as useful.
  
Login to rate this answer.
srilakshmi.b

Answered On : May 29th, 2008

View all answers by srilakshmi.b

To view the structure of the table in sql it is sp_help table_name

  
Login to rate this answer.
jrhoward

Answered On : May 30th, 2008

View all answers by jrhoward

Therefore sp_columns dataloader.rescomps will display column names and their descriptions.

  
Login to rate this answer.
cybersavvy

Answered On : Feb 5th, 2009

View all answers by cybersavvy

To display the structure of the table in SQL server , we use sp_help table_name

  
Login to rate this answer.
michealfrank

Answered On : Aug 24th, 2009

View all answers by michealfrank

It is used to view the table structure.

  
Login to rate this answer.
p24time

Answered On : Mar 6th, 2010

View all answers by p24time

Sp_help table name to describe the table

Yes  1 User has rated as useful.
  
Login to rate this answer.
raaghav

Answered On : Dec 9th, 2010

View all answers by raaghav

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'
)

  
Login to rate this answer.
vs_varun1988

Answered On : Dec 15th, 2010

View all answers by vs_varun1988

Exec sp_columns table name

  
Login to rate this answer.
papubhakta

Answered On : Apr 29th, 2011

View all answers by papubhakta

Sp_help table_name

  
Login to rate this answer.
kevaburg

Answered On : May 1st, 2011

View all answers by kevaburg

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!

  
Login to rate this answer.
suji

Answered On : Nov 5th, 2011

View all questions by suji   View all answers by suji

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

  
Login to rate this answer.
Nirav

Answered On : 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>

  
Login to rate this answer.
gangireddy

Answered On : 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_helpsp_help person

  
Login to rate this answer.
Saket Kale

Answered On : 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

  
Login to rate this answer.
Rooshi

Answered On : Oct 17th, 2014

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

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.