How to view a stored function - SQL Server

Question

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?

Why dont you use Management Studio or you certainly want to check with query? — Rolice, Mar 20 '13 at 11:18
@Rolice I want to check with a query because I am on Linux and none of the solutions I've found allow me to view the function directly. — etech, Mar 20 '13 at 11:22

Sachin · Accepted Answer · 2013-03-20T11:36:32.693

34

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'

edited Mar 20 '13 at 11:36

answered Mar 20 '13 at 11:15

Sachin

40,216
7
90
102

How can I run this when the current database is different from the target database? When I'm connected to the default database and run the above command, I get: `Error code 15250, SQL state S0001: The database name component of the object qualifier must be the name of the current database.` This works only when currently connected to DBName: `EXEC sp_helptext 'DBName.dbo.functionName'` – etech Mar 20 '13 at 11:25
try this `EXEC [ServerName].[DatabaseName].dbo.sp_HelpText 'storedProcName` – Sachin Mar 20 '13 at 11:30
1

That worked! I didn't need the server name part, though. `EXEC [DatabaseName].dbo.sp_helptext '[functionName]'` – etech Mar 20 '13 at 11:33

score 11 · Answer 2 · edited Feb 12 '15 at 12:53

11

select definition 
from sys.sql_modules 
where object_name(object_id) like 'functionName'

edited Feb 12 '15 at 12:53

Junior Mayhé

16,144
26
115
161

answered Mar 20 '13 at 11:36

Pritesh

1,938
7
32
46

you can use `like '%routineName%'` or `= 'routineName'` – Junior Mayhé Feb 12 '15 at 12:55
Note that this table does not store all types of functions. Per the doc (https://msdn.microsoft.com/en-us/library/ms175081.aspx), it includes sprocs, replication filter procedures, views, SQL DML triggers, scalar functions, TVFs, and rules. That excludes all CLR functions, CLR triggers, constraints, and extended stored procedures. – Mar 04 '15 at 18:33
Ah thank you so much! This worked for functions (Not just stored procedures) and totally saved me. <3 – Ryanman May 06 '15 at 17:45

score 2 · Answer 3 · answered Feb 21 '14 at 22:23

2

--ShowStoredProcedures
select p.[type]
      ,p.[name]
      ,c.[definition]
  from sys.objects p
  join sys.sql_modules c
    on p.object_id = c.object_id
 where p.[type] = 'P'
   --and c.[definition] like '%foo%'
ORDER BY p.[name]
___________

SELECT OBJECT_NAME(object_id) ProcedureName,
       definition
FROM sys.sql_modules
WHERE objectproperty(object_id,'IsProcedure') = 1
ORDER BY OBJECT_NAME(object_id)

answered Feb 21 '14 at 22:23

Howard Rothenburg

1,220
1
11
7

this is much better than the best answer – LearnByReading Sep 16 '16 at 21:52

score 1 · Answer 4 · answered Aug 13 '14 at 11:43

1

Yes it is working fine.

To view the stored procedures... SELECT * FROM sys.procedures;

and get procduere name and use the below query for the same(I'm using SQuirreL SQL Client Version 3.2.0-RC1).

EXEC sp_helptext 'StoredProcedureName'.

answered Aug 13 '14 at 11:43

Harshavardhan

11
1

score 1 · Answer 5 · answered Feb 13 '15 at 22:53

1

I rather use INFORMATION_SCHEMA.ROUTINES:

select ROUTINE_NAME, ROUTINE_DEFINITION, LAST_ALTERED 
from INFORMATION_SCHEMA.ROUTINES where SPECIFIC_NAME = 'usp_mysp'

Just copy the ROUTINE_DEFINITION column to a new window to see the full content.

answered Feb 13 '15 at 22:53

Hiram

2,679
1
16
15

Rinoy Ashokan · Answer 6 · 2019-02-21T12:38:06.517

1

Whether it is Stored Procedure OR Function OR any SQL object below script will give the full definition

USE<Your Data base>
SELECT OBJECT_DEFINITION (OBJECT_ID('<YOUR OBJECT NAME>')) AS ObjectDefinition

where OBJECT NAME could be your object name such as Stored Procedure / Function / Trigger ...etc name

edited Feb 21 '19 at 12:38

answered Sep 21 '18 at 07:19

Rinoy Ashokan

1,501
17
14

score 1 · Answer 7 · answered Dec 24 '20 at 06:32

1

You can go to Object Explorer, choose the Database containing the Stored Procedure and then choose 'Script Stored Procedure As ':

And then check in the destination folder you chose.

answered Dec 24 '20 at 06:32

MSIS

157
1
8

How to view a stored function - SQL Server

7 Answers7

Linked

Related