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.
EXEC sp_help 'table_name'
EXEC sp_help table_name
EXEC sp_columns 'table_name'
EXEC sp_columns table_name
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,
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,
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.