CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
frostred
frostred

Reputation: 191

Find usage of a function in SQL server

Is there a way to find a usage of a function in SQL server 2008?

Upvotes: 19

Views: 13711

Answers (4)

Nicolas
Nicolas

Reputation: 69

With SQL2012, use sp_depends in your database will list all usage inside.

ex.

  • EXEC sp_depends 'FunctionNameToSearch'
  • EXEC sp_depends 'TableNameToSearch'
  • EXEC sp_depends 'StoredProcNameToSearch'

Upvotes: 1

Cosmin Onea
Cosmin Onea

Reputation: 2738

The answer for SQL Server 2012:

SELECT DISTINCT sc.id, 
                so.name 
FROM   syscomments sc 
       INNER JOIN sysobjects so 
         ON so.id = sc.id 
WHERE  sc.TEXT LIKE '%functionname%' 
ORDER  BY 2

enter image description here

Upvotes: 17

gbn
gbn

Reputation: 432200

Use in code:

SELECT * FROM sys.sql_modules WHERE definition LIKE '%MyFunc%'
UNION
SELECT * FROM sys.computed_columns WHERE definition LIKE '%MyFunc%'
UNION
SELECT * FROM sys.check_constraints WHERE definition LIKE '%MyFunc%'
UNION
SELECT * FROM sys.default_constraints WHERE definition LIKE '%MyFunc%'

I think I've covered all bases...

You can't use sys.comments because the type is nvarchar(4000)

Upvotes: 14

Konamiman
Konamiman

Reputation: 50273

Assuming that by "usage" you mean "usage statistics", then maybe SQL Server Profiler is useful for you.

Upvotes: -1

Related Questions