:::: MENU ::::

Get code of user stored procedures and UDF in a database

  • Apr 07 / 2011
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

Get code of user stored procedures and UDF in a database

If one is required to search for any column or hard coded value in SP/UDF code then access to code definition is required. So here is a script to generate code of all SP and UDF in a database. This script may be utilize to

  • Check for any specific column or hard coded value in all SP/UDF
  • Check for dependency of any object by providing the object name as like parameter for code

Following script would generate all user SP along with their code.

 
USE [DB_Name_here]  
 GO  
 SELECT ROUTINE_NAME [SP Name], routine_definition [SP Code]  
 FROM INFORMATION_SCHEMA.ROUTINES r INNER JOIN sys.[objects] o  
 ON r.[ROUTINE_NAME] = o.[name]  
 WHERE TYPE='P'  
 AND [is_ms_shipped] = 0  
 ORDER BY routine_name  
 GO  

And with a little modification of above script we may also get list and code of all UDF in a database.

 USE [DB_Name_here]  
 GO  
 SELECT ROUTINE_NAME [UDF Name], routine_definition [UDF Code], type AS [UDF Type]  
 FROM INFORMATION_SCHEMA.ROUTINES r INNER JOIN sys.[objects] o  
 ON r.[ROUTINE_NAME] = o.[name]  
 WHERE [is_ms_shipped] = 0  
 AND type in ('FN','TF','IF')  
 ORDER BY type,routine_name  
 GO  

Do not forget to provide proper database name in use database statement.

Consult us to explore the Databases. Contact us