Jeffrey Williams wrote:
You can write code to search stored procedures ...
I had a bit of a rethink on this. My dynamic SQL issue needs solving, but no harm in sorting out any hardwired links in the SProc / VIEW code in the meantime ..
In case of interest here's what I did:
My quick search revealed a huge number of hardwired links, all the ones I looked at (until I got bored) were in comments.
Typically looking like this
-- [OTHER_DATABASE].dbo.sp_help 'TheTableName'
which is why I am getting false-hits on a global search, where these are in comments
That sort of "helper" code is in a comment to make it easy to EXEC to see what the (physical) columns are when reviewing / changing the SELECT query. (I dunno if that is a good idea, but that is how we do it ... we actually use our own Helper Sproc instead of sp_help which provides oven-ready-metadata in the formats we want it , so sp_help used here just for example - but that is the cause of the direct-reference to the database in the comments)
We have enough of these that it makes it hard to fine the (very rare) accidental-hardwired-direct-reference to database in the code
I also found a few database references that we don't have SYNONYMS for ... e.g. TEMPDB and MSDB (probably ought to have Synonyms for those ... but I am not expecting those DB names to change 🙂 )
So ... it happens that I have a "Source code compression" routine that removes extra whitespace, comments, etc. (We use it for obfuscating our code, to make it a bit harder for someone who might steal the code to then make use of it)
I used SSMS to create a script of the database objects. I choose "One file per object" so that an unclosed comment in one script didn't then cause accidental removal of follow-on material (not sure that is possible ... so just Belt and Braces)
(A number of the objects were encrypted ... 🙁 )
I then "compressed" the files and THEN searched them for the hardwired database link. Pleased to say that didn't find any that were using "dbo.", so looks like all of them are indeed using the SYNONYM.
But the risk remains ... given how similar the two are