Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find Procs/Views/Functions using particular database? Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 6:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:55 AM
Points: 417, Visits: 867
Hi 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.
Post #1379268
Posted Wednesday, October 31, 2012 6:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:55 AM
Points: 417, Visits: 867
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.
Post #1379271
Posted Wednesday, October 31, 2012 8:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 9:26 AM
Points: 208, Visits: 1,032
I 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.
Post #1379323
Posted Wednesday, October 31, 2012 8:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 13,441, Visits: 12,303
apat (10/31/2012)
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.


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1379325
Posted Wednesday, October 31, 2012 8:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:55 AM
Points: 417, Visits: 867
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.
Post #1379330
Posted Wednesday, October 31, 2012 8:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 13,441, Visits: 12,303
apat (10/31/2012)
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.


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.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1379351
Posted Wednesday, October 31, 2012 8:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:55 AM
Points: 417, Visits: 867
yes thats the plan. I just though it would be great to find out everything before hand if possible. Thanks for your help though.
Post #1379353
Posted Wednesday, October 31, 2012 8:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 13,441, Visits: 12,303
apat (10/31/2012)
yes thats the plan. I just though it would be great to find out everything before hand if possible. Thanks for your help though.


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.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1379356
Posted Wednesday, October 31, 2012 9:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 12,916, Visits: 32,077
something 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() function

here'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.

CREATE PROCEDURE pr_sample
AS SELECT * FROM DBSQL2K5.master.sys.tables

--a real table:
CREATE PROCEDURE pr_sample2
AS SELECT * FROM DBSQL2K5.DEMO1000.dbo.GMACT


SELECT objz.name,
fn.*
FROM sys.objects objz
CROSS APPLY sys.dm_sql_referenced_entities(schema_name(schema_id) + '.' + objz.name,'OBJECT')fn
where 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*/



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1379378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse