How to search for a function in the database? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to search for a function in the database?

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-03-05 : 10:59:47

How to search for a function in the database? could be in a stored procedure for example.

I mean the function is called from the database but I can't find whats calling the function

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-05 : 11:12:38

select object_name(id)
from sys.syscomments where text like '%YourFunctionName%'

This will give names of the objects which are using your function

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-05 : 11:15:15

you might also want to consider including type IF & TF

KH
[spoiler]Time is always against us[/spoiler]

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-03-05 : 11:17:31

quote:
Originally posted by raky

select name from sys.objects where type = 'fn'

ok so i put the function name in fn but do i use something else for name or just use name?

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-03-05 : 11:18:31

quote:
Originally posted by khtan

you might also want to consider including type IF & TF

KH
[spoiler]Time is always against us[/spoiler]

How do you mean?

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-03-05 : 11:19:25

quote:
Originally posted by raky

select name from sys.objects where type = 'fn'

i get error when searching...even select * from sys.objects

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-05 : 11:23:00

quote:
Originally posted by insanepaul

quote:
Originally posted by raky

select name from sys.objects where type = 'fn'

i get error when searching...even select * from sys.objects

See my edited comment and BTW are u using sql server 2000 or 2005 or 2008?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 11:36:56

quote:
Originally posted by insanepaul

quote:
Originally posted by raky

select name from sys.objects where type = 'fn'

i get error when searching...even select * from sys.objects

whats the error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-06 : 00:21:07

select
o.name, o.type, o.type_desc, o.object_id,
a.name as fromName, a.type as fromType, a.type_desc as fromTypeDesc, a.object_id as fromObjectId
from sys.all_objects o
join sys.sql_dependencies d on d.referenced_major_id= o.object_id
join sys.all_objects a on a.object_id= d.object_id
where (o.type = 'FN' OR o.type = 'TN') and o.is_ms_shipped = 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 02:06:30

quote:
Originally posted by raky

select object_name(id)
from sys.syscomments where text like '%YourFunctionName%'

This will give names of the objects which are using your function

or

select object_name(object_id) as object_names from sys.sql_modules
where object_definition(object_id) like '%function_name%'

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 09:39:39

quote:
Originally posted by madhivanan

quote:
Originally posted by raky

select object_name(id)
from sys.syscomments where text like '%YourFunctionName%'

This will give names of the objects which are using your function

or

select object_name(object_id) as object_names from sys.sql_modules
where object_definition(object_id) like '%function_name%'

Madhivanan

Failing to plan is Planning to fail

if using sql_modules no need of using object_definition function to get definition content. it already contains column definition, so you can just use


select object_name(object_id) as object_names from sys.sql_modules
where definition like '%function_name%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 09:51:24

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by raky

select object_name(id)
from sys.syscomments where text like '%YourFunctionName%'

This will give names of the objects which are using your function

or

select object_name(object_id) as object_names from sys.sql_modules
where object_definition(object_id) like '%function_name%'

Madhivanan

Failing to plan is Planning to fail

if using sql_modules no need of using object_definition function to get definition content. it already contains column definition, so you can just use

select object_name(object_id) as object_names from sys.sql_modules
where definition like '%function_name%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks. I forgot it

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 10:00:12

no problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources