apat (10/31/2012)
let me be clear, I would like to find out1. if procs within other databases on the same server is using this db.
2. procs/views/functions using this database through linked server(UNC) in queries on remote servers etc.
It is impossible to be certain that you find everything external. You didn't mention any other applications that might use this database too. There are just simply way too many places to look. It is certainly feasible to find any references from any single server but it is impossible to find any and all external references.
For #1 above use the same technique you already used.
For #2 above, if you have link servers you will have to perform the same type of searching on each of those servers. Of course in the sake of being thorough, you would have to search not only that server but any server that it has connected via link server...etc.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/