Detective Stories - Tracking Down the Database's Dependents Part I

  • Comments posted to this topic are about the item Detective Stories - Tracking Down the Database's Dependents Part I

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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.

  • 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

  • t.franz - Monday, March 6, 2017 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.

    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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin - Monday, March 6, 2017 12:02 AM

    Comments 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

  • 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.


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

  • ..."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.

  • g.britton - Monday, March 6, 2017 7:06 AM

    Brandie Tarvin - Monday, March 6, 2017 12:02 AM

    Comments 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.

    We've actually renamed databases also to see what automated processes are trying to talk to them.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • david.gugg - Monday, March 6, 2017 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.

    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?

  • david.edwards 76768 - Monday, March 6, 2017 10:04 AM

    david.gugg - Monday, March 6, 2017 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.

    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

  • 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

  • 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)

  • SoCal_DBD - Monday, March 6, 2017 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)

    Searching jobs is in part 2.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply