“I want to do X to all the Ys in database Z” – Part 1

This is a common theme that comes up, but it's never the same thing twice in a row, so it can be a challenging thing to write about canonically. So I thought I'd start a little series on how I generically approach problems that boil down to "do a thing to all the things," with some specific examples featured in each post.

A recent scenario was along these lines:

I need to refresh all the views in an arbitrary database.

Obviously we don't know all the view names in advance, especially if the user can supply any arbitrary database name at runtime. We need to dynamically reach into that database and build a list of sp_refreshview commands, one for each view. And that command also has to be executed dynamically, so we get a little Inception happening.

First, we need to make sure the database supplied is valid, online, and the user has sufficient access. We can do this by assigning the name of the database to a variable only if it matches all of the criteria:

DECLARE @dbname sysname = N'msdb', @exec nvarchar(750);
 
SELECT @exec = QUOTENAME(name)
  FROM sys.databases
  WHERE state = 0
    AND LOWER(name) = LOWER(@dbname) -- in case case sensitive
    AND HAS_DBACCESS(@dbname) = 1; 
 
IF @exec IS NULL
BEGIN
  RAISERROR('Database %s is not available.', 11, 1, @dbname);
END

Now, let's get to the fun part. If I wanted to refresh a few views manually, in a database I'm already in, and I already knew the names, I would just write this:

EXEC sys.sp_refreshsqlmodule @name = N'dbo.view1';
EXEC sys.sp_refreshsqlmodule @name = N'dbo.view2';
EXEC sys.sp_refreshsqlmodule @name = N'dbo.view3';
...

If I didn't want to look up the view names, I could write dynamic SQL. Of course I would always use schema, QUOTENAME() the views, and be very careful to use the right case and prefix Unicode strings with N, because people do unpredictably bad things with names:

USE msdb;
GO
 
DECLARE @sql nvarchar(max) = space(0);
 
SELECT @sql += N'
EXEC sys.sp_refreshsqlmodule @name = N' + char(39) -- single quote
       + QUOTENAME(s.name) + char(46)              -- dot
       + QUOTENAME(v.name) + char(39) + char(59)   -- semi-colon 
  FROM sys.views AS v 
  INNER JOIN sys.schemas AS s
     ON v.[schema_id] = s.[schema_id];
 
PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Results:

EXEC sys.sp_refreshsqlmodule @name = N'[dbo].[syspolicy_policy_category_subscriptions]';
EXEC sys.sp_refreshsqlmodule @name = N'[dbo].[sysutility_ucp_computer_cpu_health]';
EXEC sys.sp_refreshsqlmodule @name = N'[dbo].[sysutility_ucp_mi_volume_space_health]';
EXEC sys.sp_refreshsqlmodule @name = N'[dbo].[sysutility_ucp_mi_file_space_health]';
...

If I uncomment the EXEC line, of course, all those views would get refreshed.

Now, how would I make that dynamic SQL? Let's forget about the database name for the moment. In order to execute that above block dynamically, I'd need to build that all up as its own string. This is where it pays off building dynamic SQL using things like char(39), char(46), etc., because nobody likes doubling-up single quotes they already doubled up (sometimes twice):

DECLARE @outerSQL nvarchar(max) = N'
DECLARE @innerSQL nvarchar(max) = space(0);
 
SELECT @innerSQL += N''
EXEC sys.sp_refreshsqlmodule @name = N'' + char(39) -- single quote
       + QUOTENAME(s.name) + char(46)               -- dot
       + QUOTENAME(v.name) + char(39) + char(59)    -- semi-colon
  FROM sys.views AS v 
  INNER JOIN sys.schemas AS s
     ON v.[schema_id] = s.[schema_id];
 
PRINT @innerSQL;
-- EXEC sys.sp_executesql @innerSQL;';
 
EXEC sys.sp_executesql @outerSQL;

Once I have that down, I have to dynamically execute it in a specific database. In some cases I have done things like this:

SET @sql += N' FROM $db$sys.views AS v 
  INNER JOIN $db$sys.schemas AS s';
 
SET @sql = REPLACE(@sql, N'$db$', QUOTENAME(@dbname) + N'.');

But that is tedious, and also thwarts the ability to use built-in functions like OBJECT_ID() and DB_NAME() in the right database context. Many people try something like this:

EXEC @dbname.sys.sp_executesql @sql;

But this fails basic parsing, because you can't parameterize any element in a four-part name:

Msg 102, Level 15, State 1
Incorrect syntax near '.'.

A technique I picked up long ago from Erland Sommarskog is that you can parameterize the entire procedure name when using EXEC. So, for example, I can do this (I'm going to use @exec for brevity, but what that really means is the wordier @dynamic_procedure_name_to_execute):

DECLARE @exec nvarchar(750) = N'sp_who2';
 
EXEC @exec; -- is actually EXEC sp_who2;

But this isn't limited to just the name; I can also specify the other identifiers, like database and schema:

DECLARE @exec nvarchar(750) = N'master.sys.sp_who2';
 
EXEC @exec; -- is actually EXEC master.sys.sp_who2;

And I can even pass parameters:

DECLARE @exec  nvarchar(750) = N'master.sys.sp_who2',
        @param sysname       = N'active';
 
EXEC @exec @loginame = @param; -- is actually EXEC master.sys.sp_who2 @loginame = N'active';

So if we think about our objective, where we have dynamic SQL we want to dynamically execute inside an arbitrary database, we can just build the calling stored procedure name dynamically! Here's a really simple example that produces the result 4:

DECLARE @dbname sysname = N'msdb',
        @exec   nvarchar(750);
 
SELECT @exec = @dbname + N'.sys.sp_executesql'; -- becomes 'msdb.sys.sp_executesql'
 
EXEC @exec N'SELECT DB_ID();'; -- actually EXEC msdb.sys.sp_executesql N'SELECT DB_ID();';

And taking that back to our example above:

DECLARE @dbname   sysname = N'msdb',
        @exec     nvarchar(750),
        @outerSQL nvarchar(max) = N'
DECLARE @innerSQL nvarchar(max) = space(0);
 
SELECT @innerSQL += N''
EXEC sys.sp_refreshsqlmodule @name = N'' + char(39) -- single quote
       + QUOTENAME(s.name) + char(46)               -- dot
       + QUOTENAME(v.name) + char(39) + char(59)    -- semi-colon
  FROM sys.views AS v 
  INNER JOIN sys.schemas AS s
     ON v.[schema_id] = s.[schema_id];
 
PRINT @innerSQL;
EXEC sys.sp_executesql @innerSQL;';
 
SET @exec = QUOTENAME(@dbname) + N'.sys.sp_executesql';
EXEC @exec @outerSQL; -- actually EXEC msdb.sys.sp_executesql @outerSQL;

And finally, building a procedure out of it with the error handling from the beginning:

CREATE OR ALTER PROCEDURE dbo.RefreshAllViewsInDatabase
  @dbname sysname
AS
BEGIN
  SET NOCOUNT ON;
 
  DECLARE @exec nvarchar(750);
 
  SELECT @exec = QUOTENAME(name) + N'.sys.sp_executesql'
    FROM sys.databases
    WHERE state = 0
      AND LOWER(name) = LOWER(@dbname) -- in case case sensitive
      AND HAS_DBACCESS(@dbname) = 1; 
 
  IF @exec IS NULL
  BEGIN
    RAISERROR('Database %s is not available.', 11, 1, @dbname);
  END
 
  DECLARE @outerSQL nvarchar(max) = N'
    DECLARE @innerSQL nvarchar(max) = space(0);
 
    SELECT @innerSQL += N''
    EXEC sys.sp_refreshsqlmodule N'' + char(39)
         + QUOTENAME(s.name) + char(46)          
         + QUOTENAME(v.name) + char(39) + char(59)
    FROM sys.views AS v 
    INNER JOIN sys.schemas AS s
       ON v.[schema_id] = s.[schema_id];
 
    PRINT DB_NAME();
    PRINT @innerSQL;
    EXEC sys.sp_executesql @innerSQL;';
 
  EXEC @exec @outerSQL;
END
GO
 
EXEC dbo.RefreshAllViewsInDatabase @dbname = N'msdb';

You can use this technique to build up just about any execution pattern, against sets of objects, in a database determined at runtime (or all databases, in a loop). The downside is that dynamic SQL strings are hard to read and debug, and nesting them makes it even worse – especially if you use native characters like single quotes.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.