One proc that calls a proc in many other databases using a cursor - evil or just bad smell?

Question

I am doing a code review with a client. It is a proc on SQL Server 2008. He wants to do everything in SQL and not a process. He is looping through a list of catalogs and then running a proc in each one:

DECLARE CatalogList CURSOR FOR 
SELECT 'EXEC ' + c.CatalogName+ '.dbo.procInEachDb '
FROM CatalogList

FETCH NEXT FROM CatalogList INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC( @SQL)
    --PRINT @SQL
    FETCH NEXT FROM CatalogList INTO @SQL
END

I pointed out that a deadlock in one db would kill the entire process. His point is that this is code that runs every five minutes and it will just run next time.

This code smells bad, but I cannot give specific reasons. Am I wrong about this being a problem?

Answer 1

That really depends on what procInEachDb does. I can understand the use case where you have the same code in each database that you need to execute independently because it relies on locally-scoped objects, manipulates data, pushes data elsewhere, etc. Not a lot of things can be done in multiple databases from a single operation, especially if the set of databases is dynamic (arguably with a static set, you could write UNIONs across databases etc., but you still couldn't write). But I'd really try to understand from the client what the procedure does and why it is broken out this way. It may very well be the best way to do what the client needs to do, and in that case there is no bad smell at all, except that maybe it could use better error handling. =)

I manage one system where we have 500+ roughly identical databases on an instance, and we need to run the same set of regularly scheduled tasks in each one. I don't want to create 500 Agent jobs, and I don't want to create a job with 500 steps, so instead I created a stored procedure with some tweaks that behaves like sp_MSForEachDB (which is essentially the same as the code you show above). I re-use this same procedure for a lot of commands that I want to run against all of the databases. The procedure also takes arguments that allow exclusions, etc.

Yes I could have done something similar with service broker, but I find that for some things I need more visibility and control over what is actually going on. And of course doing manual one-time things is easy with tools like SQL Farms Combine or Red-Gate's multi-script tool.