How to view a SQL System UDF

Sunday, December 28, 2003

Viewing a SQL Server System UDF’s definition

 

I love User Defined Functions in SQL and am really greatful that Microsoft provided us with some great system UDF's. It bothers me however that I can't view the code in the System UDF’s provided by Microsoft.


Error Message displayed while trying to view a SQL System UDF

Don’t exactly know what it is about the above pop-up, but it’s a red flag – it makes me want to retrieve the text in ‘system_function_schema.fn_repladjustcolumnmap’ (or any other function for that matter) really badly.

Well here’s how. This script will loop through all the UDF's in the master database and list the definition. SQL System UDF's need never be a mystery any more.


use master
go
 DECLARE @functionDefination nvarchar(4000)
 DECLARE @functionName nvarchar(100)
 DECLARE function_Cursor CURSOR FOR
  SELECT ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES
      where ROUTINE_TYPE='FUNCTION'
 OPEN function_Cursor
  FETCH NEXT FROM function_Cursor INTO @functionName
  WHILE @@FETCH_STATUS = 0
 BEGIN
      select @functionDefination =  ROUTINE_DEFINITION
         from INFORMATION_SCHEMA.ROUTINES
         where ROUTINE_TYPE='FUNCTION'
         and ROUTINE_NAME = @functionName
      print @functionDefination
 FETCH NEXT FROM function_Cursor INTO @functionName
 END
CLOSE function_Cursor
DEALLOCATE function_Cursor
Go

 

by Llama Lopon

by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!