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)
⇒ "ALTER PROCEDURE" - Modifying Existing Stored Procedures in SQL Server
⇐ Generating CREATE PROCEDURE Scripts on Existing Stored Procedures in SQL Server
2017-01-05, 5962🔥, 0💬
Popular Posts:
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
What Happens to Your Transactions When ERROR 1205 Occurred in MySQL? If your transaction receives th...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...
What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS) in SQL Server? Process sqlservr.exe is the...