Collections:
sys.sql_modules - Getting Stored Procedure Definitions Back in SQL Server
How To Get the Definition of a Stored Procedure Back in SQL Server Transact-SQL?
✍: FYIcenter.com
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)
Â
2017-01-05, 3405👍, 0💬
Popular Posts:
What Are Date and Time Functions in MySQL? MySQL offers a number of functions for date and time valu...
How To Recreate an Existing Index in SQL Server? If you want to change the definition of an existing...
How To Enter Unicode Character String Literals in SQL Server Transact-SQL? Unicode characters are mu...
What Is an Oracle Instance in Oracle? Every running Oracle database is associated with an Oracle ins...
How to continue to the next iteration of a WHILE loop in SQL Server Transact-SQL? How to use CONTINU...