How to view stored procedure in SQL Server

In this SQL Server tutorial, we will learn How to view a stored procedure in SQL Server, Different ways to view a store procedure, and will cover the following topics.

• How to view stored procedure in SQL Server Management Studio
• How to view stored procedure in SQL Server using query
• How to view stored procedure dependencies in SQL Server
• How to check stored procedure in SQL Server with if exists
• How to view encrypted stored procedure in SQL Server
• How to grant view stored procedure in SQL Server
• View all stored procedure in SQL Server
• List all stored procedures in all databases SQL Server
• Role to view stored procedure in SQL Server
• Shortcut to view stored procedure in SQL Server
• Cannot view stored procedure in SQL Server

While working on projects we all might face a situation where we have to use existing stored procedures. But we don’t remember the exact usage of that stored procedure.

In such a situation, we should first view the definition of the store procedure to understand its usage. And then use it wherever required.

So, in this post, we will discuss multiple ways about how to view the stored procedures in SQL Server. Also, we will see some examples.

Now, there are mainly two ways through which we can view the definition of an existing stored procedure in SQL Server.

• The first method is by using SQL Server Management Studio. It is a software application that provides a graphical interface to manage SQL Server.
• The second method is by using Transact-SQL (T-SQL). It is an extension of the standard structured query language (SQL) created by Microsoft.

And we will discuss both methods in detail with examples in the section below.

How to view stored procedure in SQL Server Management Studio

• First, start SQL Server Management Studio and connect to the Database Engine.
• Next, go to Object Explorer, expand the database in which you have created a stored procedure, and then expand “Programmability” option.
• Next, expand “Stored Procedures”, right-click the procedure you want and then select “Script Stored Procedure as” option.

• Now, you can click one of the following options: Create To, Alter To, or Drop and Create To.
• In the end, click on “New Query Editor Window” option. It will display the procedure definition in a new query editor.

Read How to export data from SQL Server to Excel

How to view stored procedure in SQL Server using query

In SQL Server, there are 3 different ways to view the definition of a stored procedure using a query. And these different ways are listed below.

1. sp_helptext– It is a system stored procedure in SQL Server.
2. OBJECT_DEFINITION– It is a system function in SQL Server.
3. sys.sql_modules– It is a object catalog view on SQL Server.

So, in this section, we will understand all these different queries to get the procedure definition.

Using sp_helptext

Now, to view the stored procedure using sp_helptext in SQL Server, we can use the following syntax.

USE database;  
GO  
EXEC sp_helptext N'procedure_name'; 

Next, for example, consider the following query given below.

Using OBJECT_DEFINITION

To view the stored procedure using OBJECT_DEFINITION in SQL Server, we can use the following syntax.

USE database;  
GO  
SELECT OBJECT_DEFINITION (OBJECT_ID(N'procedure_name')); 

Now, for demonstration, consider the following given below.

Using sys.sql_modules

To view the stored procedure using sys.sql_modules in SQL Server, we can use the following syntax.

USE database;  
GO  
SELECT definition  
FROM sys.sql_modules  
WHERE object_id = (OBJECT_ID(N'procedure_name'));  

Now, for example, consider the following given below.

Read SQL Server Convert String to Date

How to check stored procedure in SQL Server with if exists

In SQL Server, we often get into situations where we need to run code based on whether or not a stored procedure exists. For example- creating a new procedure based upon whether it exists or not, and many more.

Now to check the existence of any object in SQL Server, we commonly use the IF EXISTS clause.

There are several techniques for identifying the existence of a stored procedure using IF EXISTS. And we will discuss most of them in the section below.

Using sys.procedures

The sys.procedures is a catalog view in SQL Server that contains all the metadata related to procedures in the current database.

And to check the existence of a procedure using sys.procedures, we have to use the following syntax.

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

In the above syntax, we have to define the database name in which we want to check the stored procedure. After this, we have to specify the name of the procedure in place of procedure_name. And it will check the existence across all schemas in the database and return “Stored Procedure Exists” if the procedure exists.

Now for demonstration, consider the following example given below.

USE sqlserverguides

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

In the example, we are checking the existence of the “MaleRecords” procedure in the sqlserverguides database. And after execution, we will get the following output.

Using sys.objects

The sys.procedures only return rows for stored procedures, whereas sys.objects view will return rows for tables, views, and other objects in addition to stored procedures. The sys.procedures inherit the sys.objects view in SQL Server. And sys.objects view is a base view whereas the sys.procedures is a derived view.

And to check the existence of a procedure using sys.objects, we have to use the following syntax.

use database
IF EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'procedure_name')
                    AND type IN ( N'P')) 
BEGIN
    PRINT 'Stored Procedure Exists'
END

In the above syntax, we have to define the database name in which we want to check the stored procedure. Next, we have to specify the name of the procedure in place of procedure_name. After this, to check a procedure of SQL Stored procedure type, we have to specify the P option in type.

Now for demonstration, consider the following example given below.

use sqlserverguides
IF EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'FemaleRecords')
                    AND type IN ( N'P')) 
BEGIN
    PRINT 'Stored Procedure Exists'
END

In the example, we are checking the existence of the “FemaleRecords” procedure in the sqlserverguides database. And after execution, we will get the following output.

Read SQL Server Convert Datetime to date

How to view stored procedure dependencies in SQL Server

It’s not just the data that’s linked in a relational database; it’s also the database objects. Tables, procedures, and views in SQL Server are frequently dependent on other objects, either because they use them, refer to them in SQL Expressions, or have constraints that access them.

So, in this section, we will learn about how to view the dependencies of a stored procedure in SQL Server.

In SQL Server, there are 2 different methods to access the dependencies of a procedure, and these methods are as follows.

1. By using SQL Server Management Studio
2. By using Transact-SQL query

Using SQL Server Management Studio

• First, run SQL Server Management Studio and connect to the Database Engine.
• Next, under Object Explorer, expand the database in which you have created a procedure, and then expand “Programmability” option.
• Next, expand “Stored Procedures”, right-click the procedure you want and then select “View Dependencies” option. It will open a “Object Dependencies” window.

• Now, in the Object Dependencies window, we have 2 options, first, objects that depend on the procedure, and second, objects on which the procedure depends.

• We can choose any of these options to view dependencies related to the selected procedure. It will display the dependences under “Dependences” column.

• In the end, click “OK” to close the window.

Using Transact-SQL

In SQL Server, there are multiple system functions and views available that we can use to get procedure dependencies. And we will most of them in the section below.

Using sys.dm_sql_referencing_entities- This is a system function in SQL Server and it is used to view the objects that depend on a procedure. And to use this function, we can follow the given syntax.

USE database;  
GO  
SELECT referencing_schema_name, referencing_entity_name, 
	   referencing_id, referencing_class_desc, is_caller_dependent  
FROM sys.dm_sql_referencing_entities ('procedure_name', 'OBJECT');   
GO  

For demonstration, consider a stored procedure “TableReturn” that uses the “MaleRecord” procedure.

CREATE PROCEDURE [dbo].[TableReturn]
AS    
   EXEC MaleRecords
RETURN  

It simply means that the procedure “TableResturn” is dependent on procedure “MaleRecords“. And now, if we use the sys.dm_sql_referencing_entities function to get its dependencies, it will return the TableReturn procedure.

USE sqlserverguides;  
GO  
SELECT referencing_schema_name, referencing_entity_name, 
	   referencing_id, referencing_class_desc, is_caller_dependent  
FROM sys.dm_sql_referencing_entities ('MaleRecords', 'OBJECT');   
GO  

In the above example, we are finding the objects that depend on the “MaleRecord” procedure that is available in the sqlserverguides database.

Using sys.dm_sql_referenced_entities– This is also a system function in SQL Server and it is used to view the objects a procedure relies on. And to use this function, we can follow the given syntax.

USE database
GO
SELECT referenced_schema_name, referenced_entity_name,  
referenced_minor_name,referenced_minor_id, referenced_class_desc,  
is_caller_dependent, is_ambiguous  
FROM sys.dm_sql_referenced_entities ('dbo.procedure_name', 'OBJECT')
GO

Now for better understanding, consider the following example given below.

USE sqlserverguides

GO
SELECT referenced_schema_name, referenced_entity_name,  
referenced_minor_name,referenced_minor_id, referenced_class_desc,  
is_caller_dependent, is_ambiguous  
FROM sys.dm_sql_referenced_entities ('dbo.MaleRecords', 'OBJECT')
GO

In the above example, we are finding the dependencies of the “MaleRecord” procedure that is available in the sqlserverguides database. As the “MaleRecods” procedure returns all the male records of a sample table. It means it depends on that sample table.

Read How to create functions in SQL Server Management Studio

How to grant view stored procedure in SQL Server

In SQL Server, we can grant permissions to either a user or a role to view the definition of an existing stored procedure. And for this implementation, we can either use SQL Server Management Studio or Transact-SQL query.

In this section, we will try to learn both methods with examples.

Using SQL Server Management Studio

• In SQL Server Management Studio, first connect to the Database Engine.
• Next, under Object Explorer, expand the database in which you have created a procedure, and then expand “Programmability” option.
• Next, expand “Stored Procedures”, right-click the procedure you want and then select “Properties” option. It will open a “Stored Procedures Properties” window.
• Now in “Stored Procedures Properties” window, select “Permissions” page. Next, to grant permission to a user or role click on “Search“. It will open “Select User or Role” window.

• Next, to add or remove users and roles, click on “Object Types..”.

• Now click the “Browse” option to view the list of users or roles. And then, select the users or roles to whom permissions should be granted.
• Now under “Explicit Permissions” grid, select the “View Definition” to grant the selected user permission to view the definition.

Using Transact-SQL

Now to grant access to view the stored procedure using Transact-SQL, we have to use the following syntax.

USE database;   
GRANT VIEW DEFINITION on procedure_name TO user_name
GO

In the above syntax, first, we need to specify the database name. After this, we have to use the GRANT statement and with this, we have to specify the procedure name and user name.

Here is a simple example demonstrated below.

USE sqlserverguides;   
GRANT VIEW DEFINITION on [dbo].[MaleRecords] TO [sqlUser]
GO

In the example, we are granting the view definition access of the MaleRecord procedure to a user named “sqlUser” in the sqlserverguides database.

Read SQL Server Convert Function + Examples

How to view encrypted stored procedure in SQL Server

There can be many situations while working in SQL Server where we want to keep some procedures, views, or functions secret. And this can be achieved in SQL Server by encrypting the data using the “WITH ENCRYPTION” clause.

The “WITH ENCRYPTION” option in SQL Server can be used with stored procedures, views, and functions. And it hides the contents of a procedure or function from discovery.

The definitions do not display in the definition column of sys.sql modules, nor can they be scripted using traditional methods in SQL Server Management Studio. This enables the admin to preserve stored procedures and functions in source control while safeguarding the intellectual property contained within.

The syntax to encrypt the store procedure in SQL Server is given below.

--Creating new procedure with encryption
CREATE PROCEDURE procedure_name WITH ENCRYPTION
AS
BEGIN
    sql_statement
END
GO

--Modifying existing procedure with encryption
ALTER PROCEDURE procedure_name WITH ENCRYPTION
AS
BEGIN
    sql_statement
END
GO

Now, if we want to view the procedure definition, we need to decrypt the stored procedure. And for this implementation, we have to follow the given steps.

• First, connect to the server instance using Dedicated Administrator Connection (DAC).
• For this, start SQL Server Management Studio and right click in an empty query editor. Next, select “Connection” option and either click on “Connect..” or click “Change Connection..“. It will open a new connection window.
• Now, enter the server name in the following way “ADMIN:instance_name“. For example, consider the image given below.

Note- Only members of the SQL Server sysadmin role can connect using the DAC.

• Now to decrypt an stored procedure, we can use the following syntax given below.

SET NOCOUNT ON
GO

ALTER PROCEDURE procedure_name WITH ENCRYPTION AS
BEGIN
 sql_statement
END 
GO

DECLARE @encrypted NVARCHAR(MAX)
SET @encrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'procedure_name' )
DECLARE @encryptedLength INT
SET @encryptedLength = DATALENGTH(@encrypted) / 2

DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N'ALTER PROCEDURE procedure_name WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))
EXEC sp_executesql @procedureHeader
DECLARE @blankEncrypted NVARCHAR(MAX)
SET @blankEncrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'procedure_name' )

SET @procedureHeader = N'CREATE PROCEDURE procedure_name WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))

DECLARE @cnt SMALLINT
DECLARE @decryptedChar NCHAR(1)
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptedMessage = ''
SET @cnt = 1
WHILE @cnt <> @encryptedLength
BEGIN
  SET @decryptedChar = 
      NCHAR(
        UNICODE(SUBSTRING(
           @encrypted, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @procedureHeader, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @blankEncrypted, @cnt, 1))
     )
  SET @decryptedMessage = @decryptedMessage + @decryptedChar
 SET @cnt = @cnt + 1
END
PRINT @decryptedMessage

The decryption process is divided into three steps.

1. First, get the procedure definition’s encrypted value from sys.sysobjvalues (via the DAC connection).
2. Second, get the encrypted value of a ‘blank’ procedure, where ‘-‘ fills in the definition.
3. Finally, obtain the blank procedure statement in plaintext and XOR them together (XOR is one of the simplest decryption procedures).

Now for demonstration, consider the following example given below.

SET NOCOUNT ON
GO

ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS
BEGIN
 PRINT 'This text is going to be decrypted'
END 
GO

DECLARE @encrypted NVARCHAR(MAX)
SET @encrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'TestDecryption' )
DECLARE @encryptedLength INT
SET @encryptedLength = DATALENGTH(@encrypted) / 2

DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N'ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))
EXEC sp_executesql @procedureHeader
DECLARE @blankEncrypted NVARCHAR(MAX)
SET @blankEncrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'TestDecryption' )

SET @procedureHeader = N'CREATE PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))

DECLARE @cnt SMALLINT
DECLARE @decryptedChar NCHAR(1)
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptedMessage = ''
SET @cnt = 1
WHILE @cnt <> @encryptedLength
BEGIN
  SET @decryptedChar = 
      NCHAR(
        UNICODE(SUBSTRING(
           @encrypted, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @procedureHeader, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @blankEncrypted, @cnt, 1))
     )
  SET @decryptedMessage = @decryptedMessage + @decryptedChar
 SET @cnt = @cnt + 1
END
PRINT @decryptedMessage

After the successful execution of the above example, we will get the following output.

Read SQL Server Substring Function [9 Examples]

View all stored procedure in SQL Server

In a production database, there can be thousands of stored procedures created by different developers. Now, what if you want to list all the stored procedures of a database.

So, in this section, we will discuss different ways to list all the stored procedures of a particular database.

Approach-1: Using SYS.OBJECTS

The SYS.OBJECTS is a catalog view in SQL Server, and it contains a row to every user-defined, schema-scoped object created within a database, including stored procedure.

Now to list all the stored procedures using SYS.OBJECTS, we have to implement the following syntax.

USE database
SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  Name
FROM sys.objects
WHERE type_desc = 'SQL_STORED_PROCEDURE'

--------------------------------------OR---------------------------------------

USE database
SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  Name
FROM sys.objects
WHERE type = 'P';

Both the given queries will return the list of all stored procedures created within the database.

Here is a simple example related to this implementation.

Approach-2: Using SYS.PROCEDURES

The SYS.PROCEDURES is also an object catalog view, and it is a subset of SYS.OBJECTS view with object type = P, X, RF, and PC. We can easily retrieve a list of all user-defined stored procedures, as well as their created and updated dates and times, using this view.

Now to use SYS.PROCEDURES to list all stored procedures, we have to follow the given syntax.

use database
SELECT
    name, type_desc, create_date, modify_date
FROM sys.procedures 

For demonstration, consider the following example given below.

Approach-3: Using INFORMATION_SCHEMA.ROUTINES

The INFORMATION_SCHEMA.ROUTINES is a system information schema view available in SQL Server. This system view offers a list of all the database’s procedures and functions. We can get simply the list of procedures by using a filter.

The ROUTINES schema view additionally displays information about the stored procedure, such as its name, definition/source code, database schema, created and changed dates, and so on.

Here is a syntax to use INFORMATION_SCHEMA.ROUTINES to list all the procedures.

USE database
SELECT
    ROUTINE_NAME, ROUTINE_DEFINITION, 
    ROUTINE_SCHEMA, SQL_DATA_ACCESS,
    CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = 'PROCEDURE'
GO

For demonstration, consider the following example in which we are listing the procedures from the sqlserverguides database.

Read Advanced Stored Procedure Examples in SQL Server

List all stored procedures in all databases SQL Server

So till now, we have learned how to list and view stored procedures in a particular sql database. Now, we will learn how to list all the stored procedures available in all the databases of a SQL Server instance.

For this implementation, we are going to execute the following statement in the query editor.

EXEC sp_msforeachdb 'USE [?]; 
	select ''?'' AS DB_name, name, object_id from sys.procedures'

By executing the above query, we are using “SELECT from sys.procedures“ for each database in a SQL Server instance. The sys.procedure helps to list all the procedures in a database, and sp_msforeachdb helps to execute the statement for every database in the instance.

But this query will also return empty results for the database that does not have any procedure.

A standard approach to overcome this issue can be as follows.

• First, create a temporary table with the specified columns
• Next, use sp_msforeachdb and “SELECT from sys.procedures“ to insert values in the temporary table.
• In the last, query the temporary table to list out all the procedures.

For demonstration, consider the following example given below.

--Creating a temporary table
CREATE TABLE #SPs (db_name varchar(100), name varchar(100), object_id int)

--Inserting values into the temporary table
EXEC sp_msforeachdb 'USE [?]; 
	INSERT INTO #SPs select ''?'', name, object_id from sys.procedures'

--Querying the table
SELECT * FROM #SPs

After successfully executing the above example, we will get a list of all the procedures from all the databases of SQL Server.

Role to view stored procedure in SQL Server

In SQL Server, the permissions related to a stored procedure can only be granted to an existing user or a role in the database.

Now, there are two types of roles that can be granted permission to view the stored procedure in SQL Server.

• First is the database role in the database
• And second is the application role in the database

SQL Server includes numerous roles, which are security principles that aggregate other principles, to make managing permissions in your databases easier.

A database role is a collection of database permissions that allows a user to perform certain tasks. Database roles can be created, modified, and removed at any time. Database roles enable you to group users into a single entity to which permissions can be applied.

An application role is a database principal that allows an application to execute with its own set of rights, similar to that of a user. Application roles can be used to restrict access to specific data to users who connect through a specific application. Application roles, unlike database roles, have no members and are inactive by default.

Read SQL Server drop table if exists

Shortcut to view stored procedure in SQL Server

By default, there is no shortcut available to view the definition of a stored procedure in SQL Server. But, by using SQL Server Management Studio, we can create our own shortcut to view the procedure definition.

Now to implement this task, we have to follow some steps specified below.

• First, start SQL Server Management Studio and go to “Tools”>”Options”. It will open a new Options window.
• Next, under the “Environment option expand “Keyboard” and click on the “Query Shortcut” option.

• Now, in the Query Shortcuts section add sp_helptext under the “Stored Procedure” column in front of an empty shortcut. In our case, we are using “Ctrl+F1”.

• In the end, Click OK to save the shortcut.
• Now, go to a query editor and select the stored procedure and press Ctrl+F1, it will return the stored procedure definition.

Cannot view stored procedure in SQL Server

Potentially, there can mainly 2 reasons due to which you are unable to view a stored procedure definition in SQL Server.

The first reason could be because you don’t have permission to view the definition of a stored procedure. To overcome this issue refer to the “How to grant view stored procedure in SQL Server” topic in this post.

The second reason could be because the stored procedure might be encrypted. Now to view an encrypted stored procedure in SQL Server, refer to the “How to view encrypted stored procedure in SQL Server” topic in this article.

You may like the following sql server articles:

• Exception Handling in SQL Server
• SQL Server convert integer to string + 12 Examples
• SQL Server Convert Datetime to String
• How to execute function in SQL with parameters
• SQL Server Replace Function
• SQL Server stored procedure naming convention and best practices
• msg 3609 the transaction ended in the trigger
• SQL Server stored procedure vs function

So in this SQL Server tutorial, we have learned How to view a stored procedure in SQL Server, and we have also covered the following topics.

• How to view stored procedure in SQL Server Management Studio
• How to view stored procedure in SQL Server using query
• How to view stored procedure dependencies in SQL Server
• How to check stored procedure in SQL Server with if exists
• How to view encrypted stored procedure in SQL Server
• How to grant view stored procedure in SQL Server
• View all stored procedure in SQL Server
• List all stored procedures in all databases SQL Server
• Role to view stored procedure in SQL Server
• Shortcut to view stored procedure in SQL Server
• Cannot view stored procedure in SQL Server