It happens many time that we don’t know the exact name of stored procedure or table or view etc. and we have to either look at object explorer and scroll all the way down for object we are looking for or we can use “Filter” facility of SSMS but I prefer one small function I have written very long back because I am a script buddy and try to avoid mouse as long as possible.
Sometime, we may want to find out whether function, table or view we are looking for, is used in any other SP or View to check dependency, I used the same function. BTW, you can use “SP_Depends” or “Information_Schema.routines” to find dependency of the object which is more accurate way for find dependency.
Here is the table valued user defined function which I have developed a long back and still using it. In fact, I am so used to it now.
CREATE FUNCTION ObjectSearch ( @SearchString VARCHAR(100) ) RETURNS TABLE RETURN SELECT DISTINCT scm.name AS SchemaName, obj.name AS ObjectName, obj.type_desc AS ObjectType FROM sys.objects obj INNER JOIN syscomments sysCom ON sysCom.Id = obj.object_id INNER JOIN sys.schemas scm ON scm.schema_id = obj.schema_id WHERE is_ms_shipped = 0 AND sysCom.text like '%' + @SearchString + '%' GO
Okay, not we have “ObjectSearch” function in our database. I have created this function in my AdventureWorks2012 database and now I am going to find where “Department” table is used so I would execute following simple SELECT statement with “ObjectSearch” function.
SELECT * FROM ObjectSearch('Department') GO
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.