Tag Archives: Sql Server

How to check if a Stored Procedure exists in Sql Server

Sql Server, , , , , , , , , , ,

Many a times we come across a scenario where we need to execute some code based on whether a Stored Procedure exists or not. There are different ways of identifying the Stored Procedure existence in Sql Server, in this article will list out the commonly used approaches. Let me know which approach you use and reason for the same.

To demonstrate these different approaches let us create a sample database with a Table and a Stored Procedure by the below script:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
CREATE TABLE dbo.Customers (CustId INT, Name NVARCHAR(50))
GO
CREATE PROCEDURE dbo.GetCustomers(@CustId AS INT)
AS
BEGIN
	SELECT * FROM  dbo.Customers WHERE CustId = @CustId
END
GO

[ALSO READ] How to check if a Table exists in Sql Server

Approach 1: Using sys.procedures catalog view

We can write a query like below to check if a GetCustomers Stored Procedure exists in the current database in any schema.

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE Name = 'GetCustomers')
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.procedures

The above query checks the existence of the GetCustomers Stored Procedure across all the schemas in the current database. Instead of this if you want to check the existence of the Stored Procedure in a specified Schema then we can re-write the above query as below:

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE object_id = OBJECT_ID(N'dbo.GetCustomers'))
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.procedures2

If you want to check the existence of a stored procedure in a database other than the current contextual database, then we can use the script like below:

USE MASTER
GO
IF EXISTS(SELECT 1 FROM SqlHintsDemoDB.sys.procedures
 WHERE object_id=OBJECT_ID(N'SqlHintsDemoDB.dbo.GetCustomers'))
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.procedures3

Note: sys.procedures catalog view contains a row for each object of the below type:

Type Description
P SQL Stored Procedure
PC Assembly (CLR) stored-procedure
RF Replication-filter-procedure
X Extended stored procedure

If you are looking for only Sql Stored Procedure, then in sys.procedures catalog views query you can add the filter AND condition as: Type = N’P’.

[ALSO READ] How to check if a record exists in table

Approach 2: Using sys.objects catalog view

sys.procedures catalog view inherits the rows from the sys.objects catalog view, sys.objects catalog view is referred to as base view where as sys.procedures is referred to as derived view. sys.procedures will return the rows only for the stored procedures whereas sys.objects view apart from returning the rows for stored procedures, it returns rows for the objects like: tables, views etc.

We can write a script like below to check the existence of a stored procedure in the current contextual database:

IF EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'dbo.GetCustomers')
                    AND type IN ( N'P', N'PC',N'X',N'RF')) 
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.objects

If you want check the existence of a stored procedure in a database other than the contextual database then we can re-write the above query by using three part naming convention as shown below:

USE master
GO
IF EXISTS (SELECT * FROM SqlHintsDemoDB.sys.objects
 WHERE object_id=OBJECT_ID(N'SqlHintsDemoDB.dbo.GetCustomers')
      AND type IN ( N'P', N'PC',N'X',N'RF')) 
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.objects2

[ALSO READ] How to check if a Temp table exists

Approach 3: Using sys.sql_modules Catalog View

We can use the sys.sql_modules catalog view to check the existence of the Stored Procedure as shown below:

USE SqlHintsDemoDB
GO
IF EXISTS (SELECT 1 FROM sys.sql_modules
   WHERE object_id =  OBJECT_ID(N'dbo.GetCustomers') 
   AND OBJECTPROPERTY(object_id, N'IsProcedure') = 1) 
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT
Check Stored Procedure Existence using sys.sql_modules

[ALSO READ] How to check if a Database exists

Approach 4: Using OBJECT_ID() function

We can use OBJECT_ID() function like below to check if a GetCustomers Stored procedure exists in the current database.

USE SqlHintsDemoDB
GO
IF OBJECT_ID(N'dbo.GetCustomers', N'P') IS NOT NULL
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using OBJECT_ID function

Specifying the Database Name and Schema Name parts for the Stored Procedure Name is optional. But specifying Database Name and Schema Name provides an option to check the existence of the stored procedure in the specified database and within a specified schema, instead of checking in the current database across all the schemas. The below query shows that, even though the current database is MASTER database, we can check the existence of the GetCustomers stored procedure in the dbo schema in the SqlHintsDemoDB database.

USE MASTER
GO
IF OBJECT_ID(N'SqlHintsDemoDB.dbo.GetCustomers', N'P')
     IS NOT NULL
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using OBJECT_ID function2

[ALSO READ] How to check if a View exists

Approach 5: Using INFORMATION_SCHEMA.ROUTINES View

We can use the INFORMATION_SCHEMA.ROUTINES view to check the existence of the stored procedure as shown below:

USE SqlHintsDemoDB
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_NAME = 'GetCustomers' 
        AND ROUTINE_TYPE = 'PROCEDURE') 
BEGIN
    PRINT 'Stored Procedure Exists'
END

RESULT
Check Stored Procedure Existence using INFORMATION_SCHEMA.ROUTINES

Approach 6: Avoid Using sys.sysobjects System table

We should avoid using sys.sysobjects System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog views sys.objects/sys.procedures/sys.sql_modules instead of sys.sysobjects system table directly to check the existence of the stored procedure.

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.sysobjects  
     WHERE id = OBJECT_ID(N'dbo.GetCustomers') AND xtype=N'P' )
BEGIN
    PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.sysobjects

[ALSO READ] :
How to check if a Database exists
How to check if a Table exists
How to check if a View exists
How to check if Temp table exists
How to check if a record exists in table

How to find all the tables with name like a given pattern in Sql Server?

Sql Server, , , , , , , ,

Many a times I come across a scenario where I will be remembering only part of the table name and need to find the complete table name. Traversing through hundreds of tables in the database and finding the exact table is boring, tedious time consuming job. In such scenarios we can use on of the below three approaches, I always use the first one as it is easy for me to remember. Let me know which approach which you use and reason for the same.

To demo this create a sample database with three tables by the below script:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
CREATE TABLE dbo.Customers (CustId INT, Name NVARCHAR(50))
CREATE TABLE dbo.CustomerOrders (OrderId INT, CustId INT)
CREATE TABLE dbo.Employee(EmpId INT, Name NVARCHAR(50))
GO

Approach 1: Using sp_tables

We can use sp_tables statement like below to find all the tables in the database whose name contains the word cust in it.

sp_tables '%cust%'

RESULT:
Table Name Like in Sql Server Using sp_tables

Approach 2: Using sys.Tables

We can use sys.tables catalog view like below to find all the tables in the database whose name contains the word cust in it.

SELECT * 
FROM sys.Tables
WHERE name LIKE '%cust%'

RESULT:
Table Name Like in Sql Server Using sys.tables

Approach 3: Using information_schema.tables

We can use information_schema.tables information schema view like below to find all the tables in the database whose name contains the word cust in it.

SELECT *
FROM information_schema.tables
WHERE table_name  LIKE '%cust%'

RESULT:
Table Name Like in Sql Server Using information_schema.tables

Dynamic PIVOT in Sql Server

Sql Server, Sql Server 2005, , , , , , ,

In the Previous Post PIVOT and UNPIVOT in Sql Server explained how PIVOT relational operator can be used to transform columns distinct values as Columns in the result set by mentioning all the distinct column values in the PIVOT operators PIVOT columns IN clause. This type of PIVOT query is called Static PIVOT query, because if the PIVOT column in the source table get’s extra unique values after the initial query then that will not reflect in the PIVOT query result unless it is mentioned in the PIVOT Columns IN clause. Static PIVOT queries are fine as long as we know that the PIVOT column values never change, for instance if PIVOT column values are MONTH or Day of the Week or hour of the day etc.

In this Article will present how we can write a Dynamic PIVOT query with an example, where we don’t need to mention the PIVOT columns each unique values and no need to worry if PIVOT column gets extra unique values after the initial query.

ALSO READ: PIVOT and UNPIVOT in Sql Server

First Create a Temporary Table #CourseSales with sample records as depicted in the below image by using the following script:

Table to be Pivoted in Sql

--Create Temporary Table #CourseSales
CREATE TABLE #CourseSales
(Course VARCHAR(50),Year INT,Earning MONEY)
GO
--Populate Sample records
INSERT INTO #CourseSales VALUES('.NET',2012,10000)
INSERT INTO #CourseSales VALUES('Java',2012,20000)
INSERT INTO #CourseSales VALUES('.NET',2012,5000)
INSERT INTO #CourseSales VALUES('.NET',2013,48000)
INSERT INTO #CourseSales VALUES('Java',2013,30000)
GO

PIVOT #CourseSales Table data on the Course column Values

Let us first understand the Static PIVOT query and then see how we can modify this Static PIVOT query to Dynamic.

Static PIVOT query

Static PIVOT Query

Below Static PIVOT script pivots the #CourseSales Table data so that the Course columns distinct values are transformed as Columns in the result set as depicted in the above image.

SELECT *
FROM #CourseSales
PIVOT(SUM(Earning) 
      FOR Course IN ([.NET], Java)) AS PVTTable

RESULT:
Static PIVOT Query In Sql Server

Let us insert one more row in the #CourseSales table for the new course SQL Server with below insert statement.

INSERT INTO #CourseSales VALUES('Sql Server',2013,15000)

Now rerun the above PIVOT query.
RESULT:
Static PIVOT Query In Sql Server

From the above result it is clear that the newly added course Sql Server sales data is not reflected in the result.

Dynamic PIVOT Query

To make the above Static PIVOT query to dynamic, basically we have to remove the hardcoded PIVOT column names specified in the PIVOT operators PIVOT columns IN clause. Below query demonstrates this.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
	FROM #CourseSales
	PIVOT(SUM(Earning) 
		  FOR Course IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

[ALSO READ] How to replace NULL value by 0 in the Dynamic Pivot result – Sql Server

RESULT:
Dynamic PIVOT Query in Sql Server

From the above result it is clear that this query is a True Dynamic PIVOT query as it reflected all the courses in the #CourseSales table without needing to write hardcoded course names in the PIVOT query.

Examples of PIVOT and Dynamic PIVOT

Below are the some of the examples of retrieving data in Sql Server using PIVOT and Dynamic PIVOT:

[ALSO READ]
PIVOT and UNPIVOT in Sql Server