How To Get the Definition of a Stored Procedure Back?
Submitted by: AdministratorIf 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 GlobalGuideLineDatabase;
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)
Submitted by: Administrator
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 GlobalGuideLineDatabase;
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)
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How to download Microsoft SQL Server 2005 Express Edition? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Get the Definition of a View Out of the SQL Server? |
☺ | How To Convert a Numeric Expression from One Data Type to Another? |
☺ | How To Use Group Functions in the SELECT Clause in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |