How To Get the Definition of a Stored Procedure Back in SQL Server Transact-SQL?

If you want get the definition of an existing stored procedure back from the SQL Server, you can use the system view called sys.sql_modules, which stores definitions of views and stored procedures.

The sys.sql_modules holds stored procedure definitions identifiable by the object id of each view. The tutorial exercise below shows you how to retrieve the definition of stored procedure, "ShowFaq" by joining sys.sql_modules and sys.procedures:

USE FyiCenterData;
GO

SELECT m.definition 
   FROM sys.sql_modules m, sys.procedures p
   WHERE m.object_id = p.object_id
   AND p.name = 'ShowFaq';   
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS BEGIN
   PRINT 'Number of questions:';
   SELECT COUNT(*) FROM Faq;
   PRINT 'First 5 questions:'
   SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq (Question VARCHAR(80));
(1 row(s) affected)

 

⇒Using Stored Procedures in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials