What is the Equivalent of DESCRIBE table in MS SQL Server

Working with a table sometimes we need to know the structure of the table i.e variable_name, variable_type, variable_size, constraint etc. In Oracle we use DESCRIBE or DESC command. But in MS SQL Server there is no command like this. We can get this facility by running some built-in store procedures or SQL query. In this tutorial we will learn about different methods to describe our specific table.

Method-1:

EXEC sp_help 'table_name'
EXEC sp_help table_name

Method-2:

EXEC sp_columns 'table_name'
EXEC sp_columns table_name

Method-3:

SELECT *
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'table_name';
ORDER BY ordinal_position;

Before starting, let’s create a table for the demonstration:

CREATE TABLE [DataAnalytics].[dbo].[tblt_emp]
(
id BIGINT NOT NULL,
emp_name VARCHAR(50) NOT NULL,
dob DATE,
age FLOAT,
income DECIMAL(10,2),
remarks NVARCHAR(500),
CONSTRAINT pk_tblt_emp_id PRIMARY KEY(id)
);

You can use the above query to get details about the table structure. I made a shorter version of the query for you for your daily life. Most of the cases my purposes are served with the below query:

SELECT ordinal_position column_sl,column_name,is_nullable,
data_type,character_maximum_length size,column_default
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblc_lapse_prevention'
ORDER BY ordinal_position

I have an another tutorial related to this tutorial Creating DESC Procedure To Describe Table in MS SQL Server where I have shown how to create procedure like Oracle’s DESCRIBE or DESC.

In this tutorial, I have shown you the built-in stored procedures or queries in MS SQL Server to describe structures of a table. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.