title: T-SQL query each database in instance without using a cursor
body: <p>Using SQL Server 2012. </p>
  
  <p>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?</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Regards, </p>
  
  <p>JC.</p>