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
 DECLARE @functionDefination nvarchar(4000)
 DECLARE @functionName nvarchar(100)
 DECLARE function_Cursor CURSOR FOR
 OPEN function_Cursor
  FETCH NEXT FROM function_Cursor INTO @functionName
      select @functionDefination =  ROUTINE_DEFINITION
         where ROUTINE_TYPE='FUNCTION'
         and ROUTINE_NAME = @functionName
      print @functionDefination
 FETCH NEXT FROM function_Cursor INTO @functionName
CLOSE function_Cursor
DEALLOCATE function_Cursor


by Llama Lopon

My Pluralsight Courses

K.Scott Allen OdeToCode by K. Scott Allen
What JavaScript Developers Should Know About ECMAScript 2015
The Podcast!