T-SQL query each database in instance without using a cursor

Question

Using SQL Server 2012.

Using the system views stored for each database I wanted to keep a summary of index behavior for all indexes across the instance. The code below collects some stats and drops them in a table for later use. The snag is, I had to use a cursor to loop through the databases in the instance. Can anyone suggest a nicer way of doing this without having to use the cursor?

USE Master
GO

-- If the procedure already exists then update it. 
IF OBJECT_ID('sp_index_stats') IS NOT NULL 
    DROP PROCEDURE sp_index_stats
GO

CREATE PROCEDURE sp_index_stats AS
    DECLARE     @dbname     NVARCHAR(128) 
    DECLARE     @finalSQL   NVARCHAR(1024)

    -- Results of the query are put into a table in Demo_DB. 
    -- So empty it now for the new results. 

    TRUNCATE TABLE Demo_DB.dbo.index_stats

    -- Use a cursor to get a list of all the tables in the instance. 
    DECLARE database_cursor CURSOR LOCAL FOR
        SELECT  name
        FROM    Master.dbo.sysdatabases
        WHERE   name NOT IN ('master', 'model', 'msdb', 'tempdb')

    OPEN database_cursor

    FETCH NEXT FROM database_cursor INTO @dbname

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Each database will contain the same structue, so I need
        -- to create a standard query just changing the database name.
        SET @finalSQL = 'INSERT INTO Demo_DB.dbo.index_stats '

        -- Sort out the select statement.
        SET @finalSQL = @finalSQL + 'SELECT sdb.name, st.name, si.name, si.type_desc, '
        SET @finalSQL = @finalSQL + 'si.is_primary_key, si.fill_factor, '
        SET @finalSQL = @finalSQL + 'sd.user_scans, sd.last_user_scan, '
        SET @finalSQL = @finalSQL + 'sd.user_updates, sd.last_user_update '

        -- The 'from' and 'joins' need to be adjusted slightly for each databse. 
        SET @finalSQL = @finalSQL + 'FROM ' + @dbname + '.sys.tables st '
        SET @finalSQL = @finalSQL + 'JOIN ' + @dbname + '.sys.indexes si ON (si.object_id = st.object_id) '
        SET @finalSQL = @finalSQL + 'JOIN ' + @dbname + '.sys.dm_db_index_usage_stats sd ON (si.object_id = sd.object_id) '
        SET @finalSQL = @finalSQL + 'JOIN ' + @dbname + '.sys.databases sdb ON (sdb.database_id = sd.database_id) ;'

        -- Ok, let's run that...
        EXEC (@finalSQL) 

        -- Get the next database in the instance..,
        FETCH NEXT FROM database_cursor INTO @dbname
    END
    CLOSE database_cursor
    DEALLOCATE database_cursor
GO

Regards,

JC.

The is an undocumented procedure named sp_MSforeachdb. Yet, it seems that fellow T-SQL programmers feel that it shouldn't be used: http://stackoverflow.com/questions/20131930/sp-msforeachdb-only-include-results-from-databases-with-results An alternative to cursors is to use `While` as described here: http://stackoverflow.com/questions/61967/is-there-a-way-to-loop-through-a-table-variable-in-tsql-without-using-a-cursor — Ralph, Oct 08 '15 at 11:07
What is the problem with the cursor? Do you think another method would be faster? Why? The main cost of this procedure at runtime is going to be the individual pulls of all that stats/metadata from each database, not the scaffolding around generating those individual queries. — Aaron Bertrand, Oct 08 '15 at 13:50
@Ralph It most definitely [should not be used](https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/). Also, [why do you think a while loop is any different from a cursor](https://sqlblog.org/blogs/aaron_bertrand/archive/2012/01/26/the-fallacy-that-a-while-loop-isn-t-a-cursor.aspx)? — Aaron Bertrand, Oct 08 '15 at 13:52
@AaronBertrand: I don't think that loops are better than cursors. Nor do I believe the opposite. But as far as I can tell the discussion on the web on this topic is somewhat controversial: some favor cursors, some favor loops [with table variables and PK](http://blog.sqlconcepts.co.nz/2012/07/cursors-and-while-loops-reply.html), and some say it depends on the case (server version, setup, volume, etc.). James asked for an alternative to a cursor and I made him aware of an alternative (without assessing it at the same time). I hope that's OK. — Ralph, Oct 08 '15 at 14:09
@Ralph it's just a common misconception that while loops are faster than cursors. Yes, it's an alternative, kind of like a red Ferrari is an alternative to a yellow Ferrari. — Aaron Bertrand, Oct 08 '15 at 14:13
My SQL knowledge comes from an Oracle development background where the use of cursors was fairly standard. In the SQL server world cursors are considered bad...http://stackoverflow.com/questions/58141... My code works, but the use of cursors makes it appear just be be poor coding for a SQL Server solution. — James Crabtree, Oct 09 '15 at 08:14

score 0 · Accepted Answer · answered Oct 08 '15 at 16:00

What about concatenation?

Something like this (not reproduced the whole stored procedure, but hopefully you get the gist)

DECLARE @SQL VARCHAR(MAX)

SET @SQL = ''

SELECT @SQL = @SQL + 
'INSERT INTO Demo_DB.dbo.index_stats ' +
'SELECT sdb.name, st.name, si.name, si.type_desc, ' +
'si.is_primary_key, si.fill_factor, ' +
'sd.user_scans, sd.last_user_scan, ' +
'sd.user_updates, sd.last_user_update ' +
'FROM ' + name + '.sys.tables st ' +
'JOIN ' + name + '.sys.indexes si ON (si.object_id = st.object_id) ' +
'JOIN ' + name + '.sys.dm_db_index_usage_stats sd ON (si.object_id = sd.object_id) ' +
'JOIN ' + name + '.sys.databases sdb ON (sdb.database_id = sd.database_id) ;'  
FROM    Master.dbo.sysdatabases
WHERE   name NOT IN ('master', 'model', 'msdb', 'tempdb')

SELECT @SQL

(Obviously not EXECing anything at this point).

Thank you Fruitbat, that's exactly it. Aside from the debate over cursors and loops, when I wrote the code out I was sure there was a _better_ SQL solution, but couldn't see it. — James Crabtree, Oct 09 '15 at 08:24

T-SQL query each database in instance without using a cursor

1 Answers1