SQL list of all the user defined functions in a database

sql-server sql-server-2008-r2
stackuser83 picture stackuser83 · Mar 14, 2013 · Viewed 96.1k times · Source

I am looking for a SQL query that outputs the function definitions for all of the user defined functions in a database catalog.

I have found as far as

SELECT OBJECT_DEFINITION (OBJECT_ID(N'dbo.UserFunctionName')) AS [Object Definition]

and

SELECT ROUTINE_NAME FROM information_schema.routines WHERE routine_type = 'function'

but I can't think of or find a way to feed the ROUTINE_NAME list to the OBJECT_ID.

The purpose here is a searchable text of the user defined function definitions in a database for database change analysis, if something like a full SQL procedure or purposed helper program is easier, I will do that and post it.

Answer

RandomUs1r picture RandomUs1r · Mar 14, 2013 
SELECT name, definition, type_desc 
  FROM sys.sql_modules m 
INNER JOIN sys.objects o 
        ON m.object_id=o.object_id
WHERE type_desc like '%function%'

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More
CREATE TABLE IF NOT EXISTS equivalent in SQL Server

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Read More