April 19, 2012 at 10:46 am
I have 2 databases, DB A and DB B. I have stored Procedures on DB A and I need to find which ones are referenceing tables on DB B. Can this be done? If so, how?
April 19, 2012 at 10:58 am
well there are to options, opening every SP and looking through it or something like the following may work:
SELECT SPECIFIC_NAME FROM DBa.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%DBb%'
that will select the names of the stored procedures where DBb is in the first 8000 characters of the definition of the SP (the code). the link below has a little more information
http://www.bradleyschacht.com/search-stored-procedure-text/
EDIT: amazing what google can find these days.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 19, 2012 at 11:04 am
You can search the definition column in the sys.all_sql_modules view with something similar to this:
SELECT OBJECT_NAME(object_id) FROM sys.all_sql_modules
WHERE definition LIKE '%Database_A%'
This won't limit the results to database references (or procedures for that matter), but it is a start. If your databases are named 'A' or 'B', you're probably in for a long day. :hehe:
April 19, 2012 at 11:08 am
Here are a couple of links that might help. Not tried but you need to slightly modify the code to add your second database.
1. Find all tables not referenced in stored procedures
2. Find All SQL Server Stored Procedures Containing Table Reference
3. Find Stored Procedure Related to Table in Database – Search in All Stored Procedure[/url]
======================================
Blog: www.irohitable.com
April 19, 2012 at 11:09 am
Steve Cullen (4/19/2012)
You can search the definition column in the sys.all_sql_modules view with something similar to this:
SELECT OBJECT_NAME(object_id) FROM sys.all_sql_modulesWHERE definition LIKE '%Database_A%'
This won't limit the results to database references (or procedures for that matter), but it is a start. If your databases are named 'A' or 'B', you're probably in for a long day. :hehe:
i actually like that better than the solution i posted. still learning all the sys. ?tables? ;-):w00t:;-)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 19, 2012 at 11:11 am
May i add another method?
SELECT DISTINCT OBJECT_NAME (SC.id)
FROM syscomments sc
WHERE sc.text LIKE '%DatabaseA%'
April 19, 2012 at 12:20 pm
I use this:
USE dbName
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE '%whatever%'
ORDER BY SO.Name
Jared
CE - Microsoft
April 20, 2012 at 4:36 am
Or you could just download a trial third party utility that should be able to to do the dependency analysis for you 🙂
I think ApexSql and Redgate have utilities that can do things like this.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply