March 6, 2017 at 12:02 am
Comments posted to this topic are about the item Detective Stories - Tracking Down the Database's Dependents Part I
March 6, 2017 at 2:25 am
Your code, where you select for definition like '%' + @ValueToFind + '%' is wrong, since it would return references to MyDB10 and MyDB1_test and dev_MyDB10 when @ValueToFind is 'MyDB1'.
I did not test it, but something as LIKE '%[ .' + CHAR(9) + CHAR(13) + CHAR(10) + '\["]' + @ValueToFind + '[ .' + CHAR(9) + CHAR(13) + CHAR(10) + '\]"]' ESCAPE '\' could work better (space, dot, tab, carriage return, line feed, square brackets or double quotes are accepted as delimiter. could work better (space, dot, tab, carriage return, line feed, square brackets or double quotes are accepted as delimiter.
God is real, unless declared integer.
March 6, 2017 at 3:14 am
Also, syscomments.text is confined to 4000 characters, so large stored procedures and functions will get cut off. If the database name reference is past character 4000, you’re out of luck on this search
Brandie
Thanks for the article. The text in syscomments actually spills on to the next row if the definition is more than 4000 characters, so the issue isn't that you miss everything past the 4000th character, but rather that there's a small chance that the text you're searching for may be split across two rows.
John
March 6, 2017 at 4:59 am
t.franz - Monday, March 6, 2017 2:25 AMYour code, where you select for definition like '%' + @ValueToFind + '%' is wrong, since it would return references to MyDB10 and MyDB1_test and dev_MyDB10 when @ValueToFind is 'MyDB1'.I did not test it, but something as
LIKE '%[ .' + CHAR(9) + CHAR(13) + CHAR(10) + '\["]' + @ValueToFind + '[ .' + CHAR(9) + CHAR(13) + CHAR(10) + '\]"]' ESCAPE '\' could work better (space, dot, tab, carriage return, line feed, square brackets or double quotes are accepted as delimiter.could work better (space, dot, tab, carriage return, line feed, square brackets or double quotes are accepted as delimiter.
The code isn't actually wrong. I wrote it this way on purpose. I would rather be too broad and then delete references I don't need then accidentally drop occurrences of what I'm looking for, which adding delimiters might do.
March 6, 2017 at 7:06 am
Brandie Tarvin - Monday, March 6, 2017 12:02 AMComments posted to this topic are about the item Detective Stories - Tracking Down the Database's Dependents Part I
My approach is a bit simpler. I don't remove the database, I just deny all access to it, then wait for the phone to ring. If no one calls after a complete fiscal year is done, I back it up to tape and drop it.
Gerald Britton, Pluralsight courses
March 6, 2017 at 7:58 am
I believe another way to do this is with Redgate's free SQL Search tool. I regularly use this tool if I need to change something and I am unsure of the object's dependencies.
March 6, 2017 at 8:36 am
..."people who say sp_MSforeachdb actually skips databases or items."
I have found that if you are not fully authorized on a server or db, system procs and views may quietly exclue or omit the data, so it can be impossible to know if your results are complete.
March 6, 2017 at 9:52 am
g.britton - Monday, March 6, 2017 7:06 AMBrandie Tarvin - Monday, March 6, 2017 12:02 AMComments posted to this topic are about the item Detective Stories - Tracking Down the Database's Dependents Part IMy approach is a bit simpler. I don't remove the database, I just deny all access to it, then wait for the phone to ring. If no one calls after a complete fiscal year is done, I back it up to tape and drop it.
We've actually renamed databases also to see what automated processes are trying to talk to them.
March 6, 2017 at 10:04 am
david.gugg - Monday, March 6, 2017 7:58 AMI believe another way to do this is with Redgate's free SQL Search tool. I regularly use this tool if I need to change something and I am unsure of the object's dependencies.
I'll second that, an invaluable tool.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
March 6, 2017 at 1:19 pm
david.edwards 76768 - Monday, March 6, 2017 10:04 AMdavid.gugg - Monday, March 6, 2017 7:58 AMI believe another way to do this is with Redgate's free SQL Search tool. I regularly use this tool if I need to change something and I am unsure of the object's dependencies.I'll second that, an invaluable tool.
good tool to be sure, but cannot find dependencies outside the server, but then, nothing can do that. It's an NP-complete problem.
Gerald Britton, Pluralsight courses
March 6, 2017 at 4:09 pm
ckpds - Monday, March 6, 2017 8:36 AM..."people who say sp_MSforeachdb actually skips databases or items."I have found that if you are not fully authorized on a server or db, system procs and views may quietly exclue or omit the data, so it can be impossible to know if your results are complete.
I've seen the issue with sp_MSforeachdb and skipping databases. It really does happen. One of the primary problems is how the cursor is not a static cursor,
There was a connect item on it awhile back, another one that MS acknowledged but won't fix because the stored procedure is undocumented, for internal use:
Disable sp_msforeachdb by default, or fix it
Sue
March 6, 2017 at 4:25 pm
I have one additional check which is in the jobs. This is the portion of my script that checks for that:
-- Look for references to the object in any jobs
select @SQL = 'insert into #Results '
select @SQL = @SQL + 'SELECT db_name(), ''Job'', ''Job: '' + SJ.name, ''Step: '' + right(''00'' + convert(varchar, SJS.step_id), 2) '
select @SQL = @SQL + 'FROM msdb.dbo.sysjobs SJ (nolock) '
select @SQL = @SQL + 'INNER JOIN msdb.dbo.sysjobsteps SJS (nolock) ON (SJ.job_id = sjs.job_id) '
select @SQL = @SQL + 'where command LIKE ''%' + @SearchString + '%'''
exec(@SQL)
March 7, 2017 at 6:30 am
SoCal_DBD - Monday, March 6, 2017 4:25 PMI have one additional check which is in the jobs. This is the portion of my script that checks for that:
-- Look for references to the object in any jobs
select @SQL = 'insert into #Results '
select @SQL = @SQL + 'SELECT db_name(), ''Job'', ''Job: '' + SJ.name, ''Step: '' + right(''00'' + convert(varchar, SJS.step_id), 2) '
select @SQL = @SQL + 'FROM msdb.dbo.sysjobs SJ (nolock) '
select @SQL = @SQL + 'INNER JOIN msdb.dbo.sysjobsteps SJS (nolock) ON (SJ.job_id = sjs.job_id) '
select @SQL = @SQL + 'where command LIKE ''%' + @SearchString + '%'''exec(@SQL)
Searching jobs is in part 2.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply