How to view a stored function - SQL Server

sql-server stored-functions
etech picture etech · Mar 20, 2013 · Viewed 79.3k times · Source

Version: SQLServer 8

I would like to view the contents of a stored function in sqlserver, i.e. what exactly the function is doing.

None of the options listed here work for me. There doesn't appear to be any database/table called sys.objects. I was able to query the information_table.routines table, but that does not contain the function that I am looking for. My function is located in:

DBName.dbo.functionName

How can I view the contents of this function?

Answer

Sachin picture Sachin · Mar 20, 2013

You can use sp_helptext command to view the definition. It simply does

Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

E.g;

EXEC sp_helptext 'StoredProcedureName'

EDIT: If your databases or server are different then you can do it by specifying them as well

EXEC [ServerName].[DatabaseName].dbo.sp_helptext 'storedProcedureName'

Related questions

SQL Server - where is "sys.functions"?

SQL Server 2005 has great sys.XXX views on the system catalog which I use frequently. What stumbles me is this: why is there a sys.procedures view to see info about your stored procedures, but there is no sys.functions …

Read More
Stored Procedure to Open and Read a text file

I am looking for a stored procedure code that will open a text file, read in several thousand lines, and add the code to a table in the database. Is there a simple way to implement this in T-SQL?

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More