What is its purpose?How to use it?

Read Best Answer

Editorial / Best Answer

suji  

 Profile Answers by suji Questions by 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
sp_help 'TABLE_NAME';

If you would like to see more details, Create your custom procedure

Code
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

Here is another alternative way to get the same information

Code
sp_columns 'TableName' (e.g. sp_columns 'Employee')
sp_columns [ @table_name = ] object [ , [ @table_owner = ] owner ] 
[ , [ @table_qualifier = ] qualifier ] 
[ , [ @column_name = ] COLUMN ] 
[ , [ @ODBCVer = ] ODBCVer ]

Here is the usage :

Code
EXEC sp_columns @table_name = 'Department', @table_owner = 'sa';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName'

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

Rooshi

• Oct 17th, 2014

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

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

Improve Answer