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:

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:

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.