How To Get the Definition of a User Defined Function Back in SQL Server Transact-SQL?

If you want get the definition of an existing user defined function back from the SQL Server, you can use the system view called sys.sql_modules, which stores definitions of functions, stored procedures, and views.

The sys.sql_modules holds user defined function definitions identifiable by the object id of each function. The tutorial exercise below shows you how to retrieve the definition of stored procedure, "Sundays" by joining sys.sql_modules and sys.objects:

USE FyiCenterData;
GO

SELECT m.definition 
   FROM sys.sql_modules m, sys.objects o
   WHERE m.object_id = o.object_id
   AND o.name = 'Sundays';   
GO
definition
-----------------------------------------------
CREATE FUNCTION Sundays()
   RETURNS INT
   AS BEGIN
      DECLARE @date DATETIME;
      DECLARE @count INT;
      SET @date = '2006-12-31'; 
      SET @count = 0;
      WHILE DATEPART(YEAR, @date) <= 2008 BEGIN
         SET @date = DATEADD(DAY, 1, 
(1 row(s) affected)

⇒ "ALTER FUNCTION" - Modifying Existing Functions in SQL Server

⇐ Generating CREATE FUNCTION Scripts on Existing Functions in SQL Server

⇑ Using User Defined Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials