InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:08:09 GMT20http://www.sqlservercentral.com/Forums/Topic1379268-391-1.aspxsomething that might help a little bit:if you create a procedure that references a non-sys object in another database, you can find it via the sys.dm_sql_referenced_entities() functionhere's an example...the server/database/table actually exists as a linked server on my system:note how it does NOT find the proc that is referencing sys.tables, for example...a search of the sys.sql_modules (better than syscomments) would find it...but that also could give a false positive if the server name is in a comment.[code]CREATE PROCEDURE pr_sampleAS SELECT * FROM DBSQL2K5.master.sys.tables--a real table:CREATE PROCEDURE pr_sample2AS SELECT * FROM DBSQL2K5.DEMO1000.dbo.GMACTSELECT objz.name,fn.* FROM sys.objects objz CROSS APPLY sys.dm_sql_referenced_entities(schema_name(schema_id) + '.' + objz.name,'OBJECT')fnwhere name LIKE 'pr_sample%' /*name referencing_minor_id referenced_server_name referenced_database_name referenced_schema_name referenced_entity_name referenced_minor_name referenced_id referenced_minor_id referenced_class referenced_class_desc is_caller_dependent is_ambiguous------------- -------------------- ----------------------- -------------------------- ---------------------- ----------------------- ---------------------- ------------- ------------------- ---------------- ---------------------- ------------------- ------------pr_sample2 0 DBSQL2K5 DEMO1000 dbo GMACT NULL NULL 0 1 OBJECT_OR_COLUMN 0 0*/ SELECT objz.name,fn.* FROM sys.objects objz CROSS APPLY sys.dm_sql_referencing_entities(schema_name(schema_id) + '.' + objz.name,'OBJECT')fn where name LIKE 'pr_sample%' /(no results*/[/code]Wed, 31 Oct 2012 09:16:25 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1379268-391-1.aspx[quote][b]apat (10/31/2012)[/b][hr]yes thats the plan. I just though it would be great to find out everything before hand if possible. Thanks for your help though.[/quote]At best you can probably find most things. In my experience there is always at least 1 that gets missed because of something really strange like some application that has something buried in a pass through query that has been compiled and the dev is no longer around, that type of thing. Best of luck, it is always a challenge to take down a db no matter how much effort is expended up front.Wed, 31 Oct 2012 08:32:18 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1379268-391-1.aspxyes thats the plan. I just though it would be great to find out everything before hand if possible. Thanks for your help though.Wed, 31 Oct 2012 08:29:05 GMTapathttp://www.sqlservercentral.com/Forums/Topic1379268-391-1.aspx[quote][b]apat (10/31/2012)[/b][hr]Thanks for the reply Brendan & Sean. I will use syscomments for sure. Just that we are planning to get rid of/move one of the databases and need to find out what all is accessing it locally or remotely.[/quote]I would recommend that once you have identified what you think is everything that is referencing this db and you are ready to remove it, instead just take it offline. You will quickly find any of the other applications that you missed. ;-)Wed, 31 Oct 2012 08:27:03 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1379268-391-1.aspxThanks for the reply Brendan & Sean. I will use syscomments for sure. Just that we are planning to get rid of/move one of the databases and need to find out what all is accessing it locally or remotely.Wed, 31 Oct 2012 08:10:15 GMTapathttp://www.sqlservercentral.com/Forums/Topic1379268-391-1.aspx[quote][b]apat (10/31/2012)[/b][hr]let me be clear, I would like to find out 1. 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.[/quote]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.Wed, 31 Oct 2012 08:03:20 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1379268-391-1.aspxI would look into SQL Search by Redgate and see if that does what you're looking for. It's a free plugin for SSMS. If that doesn't work there may be other ways to get what you're looking for but I would start there first.Wed, 31 Oct 2012 08:00:42 GMTbrendan woulfehttp://www.sqlservercentral.com/Forums/Topic1379268-391-1.aspxlet me be clear, I would like to find out 1. 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.Wed, 31 Oct 2012 06:12:26 GMTapathttp://www.sqlservercentral.com/Forums/Topic1379268-391-1.aspxHi All,I would like to find out all procs, views, functions, jobs, SSIS packages everything which is using particular database name within it. For example a job may be using XYZ database within one of the steps to execute SQL or ssis package etc. I think I can find out the procs using syscomments table but dont know if there is a better way to find this. please suggest.Wed, 31 Oct 2012 06:07:30 GMTapat