October 31, 2012 at 6:07 am
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.
October 31, 2012 at 6:12 am
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.
October 31, 2012 at 8:00 am
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.
October 31, 2012 at 8:03 am
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/
October 31, 2012 at 8:10 am
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.
October 31, 2012 at 8:27 am
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 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/
October 31, 2012 at 8:29 am
yes thats the plan. I just though it would be great to find out everything before hand if possible. Thanks for your help though.
October 31, 2012 at 8:32 am
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 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/
October 31, 2012 at 9:16 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply