Creating DESC Procedure To Describe Table in MS SQL Server

In Oracle it is easy to see the structure of a table by executing DESCRIPTION or DESC command. In SQL there is no such command. You have to use some built-in procedures or run some queries to see your desired table structure. I have shown some built-in stored procedures and queries to see the structure of a table in another tutorial. But today I’ll show you how you can create DESC procedure to describe any table in MS SQL Server.

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

Let’s execute the following script to drop existing procedure with same name.

USE DataAnalytics;
GO
IF OBJECT_ID('mrk_des', 'P') IS NOT NULL
  DROP PROCEDURE mrk_des
GO

Now, let’s write the procedure mrk_des which will describe any table in SQL Server:

CREATE PROCEDURE [dbo].[mrk_des]
(
@table_name nvarchar(max)
)
AS
BEGIN
SELECT
ordinal_position column_sl,
column_name,
is_nullable,
CASE data_type
    WHEN 'nvarchar' THEN 'nvarchar('+CAST((character_maximum_length / 2) as varchar)+')'
    WHEN 'varchar' THEN 'varchar('+CAST(character_maximum_length as varchar)+')'
    WHEN 'char' THEN 'char('+CAST(character_maximum_length as varchar)+')'
    WHEN 'nchar' THEN 'nchar('+CAST((character_maximum_length / 2) as varchar)+')'
    WHEN 'binary' THEN 'binary('+CAST(character_maximum_length as varchar)+')'
    WHEN 'varbinary' THEN 'varbinary('+CAST(character_maximum_length as varchar)+')'
    WHEN 'numeric' THEN 'numeric('+CAST(numeric_precision as varchar)+
(CASE WHEN numeric_scale = 0 THEN '' ELSE ','+CAST(numeric_scale as varchar) END) +')'
    WHEN 'decimal' THEN 'decimal('+CAST(numeric_precision as varchar)+
(CASE WHEN numeric_scale = 0 THEN '' ELSE ','+CAST(numeric_scale as varchar) END) +')'
    ELSE data_type
    END data_type,        
column_default
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @table_name
ORDER BY
1
END
;

After executing the above script you will see a success message which means your procedure mrk_des created successfully. To use the procedure you can follow one of the below two methods:

--Method-1
EXEC mrk_des 'tblt_emp';
--or
--Method-2
mrk_des 'tblt_emp';

After executing one of the above statement we will see the following table with our desired result:

column_sl column_name is_nullable data_type column_default
1 id NO bigint NULL
2 emp_name NO varchar(50) NULL
3 dob YES date NULL
4 age YES float NULL
5 income YES decimal(10,2) NULL
6 remarks YES nvarchar(250) NULL

I’ll create another procedure mrk_desc here with two more extra features. So, let’s follow the procedures.

Let’s execute the following script to drop existing procedure with same name as previous example.

USE DataAnalytics;
GO
IF OBJECT_ID('mrk_desc', 'P') IS NOT NULL
  DROP PROCEDURE mrk_desc
GO

Now, let’s write the procedure mrk_desc which will describe any table in SQL Server:

CREATE PROCEDURE [dbo].[mrk_desc] (
@table_name nvarchar(max)
)
AS
BEGIN
SELECT
col.column_id,
col.name table_name,
CASE typ.name
WHEN ‘nvarchar’ THEN ‘nvarchar(‘+CAST((col.max_length / 2) as varchar)+’)’
WHEN ‘varchar’ THEN ‘varchar(‘+CAST(col.max_length as varchar)+’)’
WHEN ‘char’ THEN ‘char(‘+CAST(col.max_length as varchar)+’)’
WHEN ‘nchar’ THEN ‘nchar(‘+CAST((col.max_length / 2) as varchar)+’)’
WHEN ‘binary’ THEN ‘binary(‘+CAST(col.max_length as varchar)+’)’
WHEN ‘varbinary’ THEN ‘varbinary(‘+CAST(col.max_length as varchar)+’)’
WHEN ‘numeric’ THEN ‘numeric(‘+CAST(col.precision as varchar)+
(CASE WHEN col.scale = 0 THEN ” ELSE ‘,’+CAST(col.scale as varchar) END) +’)’
WHEN ‘decimal’ THEN ‘decimal(‘+CAST(col.precision as varchar)+
(CASE WHEN col.scale = 0 THEN ” ELSE ‘,’+CAST(col.scale as varchar) END) +’)’
ELSE typ.name
END data_type,
CASE WHEN col.is_nullable = 0 THEN ‘NOT NULL ‘ ELSE ” END +
CASE WHEN col.is_identity = 1 THEN ‘IDENTITY’ ELSE ” END constraints,
ISNULL((SELECT ‘y’
FROM sys.key_constraints kc
INNER JOIN
sys.tables tb ON tb.object_id = kc.parent_object_id INNER JOIN
sys.indexes si ON si.name = kc.name
INNER JOIN
sys.index_columns sic
ON sic.index_id = si.index_id AND sic.object_id = si.object_id
WHERE kc.type = ‘PK’
AND tb.name = @table_name
AND sic.column_id = col.column_id),”) pk,
ISNULL((SELECT (SELECT name FROM sys.tables st WHERE st.object_id = fkc.referenced_object_id)
FROM sys.foreign_key_columns fkc INNER JOIN
sys.columns c
ON c.column_id = fkc.parent_column_id AND fkc.parent_object_id = c.object_id
INNER JOIN
sys.tables t ON t.object_id = c.object_id
WHERE t.name = tab.name
AND c.name = col.name),”) fk,
ISNULL(col.collation_name,”) collation,
ISNULL((SELECT value
FROM sys.extended_properties
WHERE major_id = tab.object_id
AND minor_id = col.column_id),”) comments
FROM
sys.columns col
INNER JOIN
sys.tables tab
ON
tab.object_id = col.object_id
INNER JOIN
sys.types typ
ON
typ.system_type_id = col.system_type_id
WHERE
tab.name = @table_name AND typ.name != ‘sysname’
ORDER BY
col.column_id
END
;

After executing the above script you will see a success message which means your procedure created successfully. Now it’s time to use our own created procedure. You can run the procedure in two ways as below:

--Method-1
EXEC mrk_desc 'tblt_emp';
--or
--Method-2
mrk_desc 'tblt_emp';

After executing one of the above statement we will see the following table with your desired result:

column_id table_name data_type constraints pk fk collation comments
1 id bigint NOT NULL y
2 emp_name varchar(50) NOT NULL SQL_Latin1_General_CP1_CI_AS
3 dob date
4 age float
5 income decimal(10,2)
6 remarks nvarchar(500) SQL_Latin1_General_CP1_CI_AS

Please note that after executing the below script your mrk_desc will marked as system procedure, you can even leave the exec command, too (not recommended anyway):

EXEC sys.sp_MS_marksystemobject mrk_desc
mrk_desc 'tbla_hst_gross_add_policy';

I have an another tutorial related to this tutorial What is the Equivalent of DESCRIBE table in MS SQL Server where I have shown the built-in stored procedures and queries which are used to describe the structure of a table in MS SQL Server.

In this tutorial, I have shown you how to create procedure as like as Oracle’s DESC command in MS SQL Server.¬†Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.

Add a Comment