I don’t know about you but every SQL Server environment I’ve ran into is extremely well documented and easy to figure out the dependencies.
…And then I wake up.
Documented SQL Servers?
Ever have a question about where in your SQL Server stored procedures you used that particular piece of text?
Maybe you need to know where you used that CLR integrated function call in your SQL Server code base?
I recently began a new job and while getting myself up to speed on their, I mean our database environment I discovered that the server had CLR integrated functions. Where are these being used? Nobody on the team could give me a straight answer.
sp_MSForeachdb to the rescue
This built-in little gem (undocumented) will loop through all your databases on a server. It takes in a string as a command with the ? as a placeholder for the table name.
sp_MSForeachdb ' use ?; SELECT DISTINCT ''?'' as DbName, o.name AS OBJ_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE (m.definition LIKE ''%YourTextToFind%'' )'
This will return an empty result set if the database does not have YourTextToFind in any of it’s stored procedures, views and functions.
When it does find it will look something like:
|DbName ||OBJ_Name ||type_desc |
|DatabaseName ||SP, View, Function Name ||SQL_STORED_PROCEDURE, VIEW, SQL_SCALAR_FUNCTION |
Now you are armed with a list of usages/dependencies. It’s a simple man’s database documentation tool.
I imagine the above code could be better written. After all, I’m just an accidental DBA.
Here’s some cool usages floating around on the interwebs.
Find the space used for each database.
Six common uses of sp_MSforeachdb.
There’s also sp_MSforeachtable…. I think you can guess just what you might do with that.