Query to Open the Stored Procedure Definition

Today I came across one scenario where I need to get the stored procedure definition from the Admin panel of the DNN(Dot Net Nuke) site with out opening the SQL Server, which is possible using below query, 
  1. select definition  
  2. rom sys.Sql_modules  
  3. where object_id=object_ID('YourSchemaname.ProcedureName')  
For Example,
I have Test database which consist of userprofile table and the stored procedure Usp_GetUser as show in below figure
The definition of the procedure Usp_GetUser 
  1. ALTER procedure [dbo].[Usp_GetUser]  
  2. as  
  3. begin  
  4. select*From UserProfile  
  5. end  
Now we are going the see how to get the definition of the above procedure using the query, 
  1. select definition  
  2. from sys.Sql_modules  
  3. where object_id=object_ID('dbo.Usp_GetUser')  
I hope you enjoyed this blog. Your valuable feedback, question, or comments about this blog are always welcomed.

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now