Is there a way to find a usage of a function in SQL server 2008?
Add a comment
|
4 Answers
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
3 Comments
GWR
Per @gbn, You can't use sys.comments because the type is nvarchar(4000)
Cosmin Onea
Have you tested it before down voting? I just tested it, syscomments definitely works.
GWR
Yes. if the text you are looking for is beyond the first 4000 characters, it would not be found in the search.
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)
2 Comments
Johnny_D
Thanks for your reply. Does it really cover everying (table, function, procedures and triggers)?
CalvinDale
To avoid
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists., replace the * with CONCAT(OBJECT_SCHEMA_NAME(object_id), N'.', OBJECT_NAME(object_id)) AS SchemaObjectNameWith 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'
Comments
Assuming that by "usage" you mean "usage statistics", then maybe SQL Server Profiler is useful for you.
