SQL Server – Using Dynamic Queries Across Multiple Databases to Change Views

dynamic-sqlsql server

I am seeking to run a query to modify views across multiple databases (See the initial question here.) and was able to find how to get it. I've now run into the problem that if I run this from master (Where I'm sure it has to run from) i receive null results. However, if i go to a specific database and run the dynamic query there, i get the views i'm looking to modify (the intended result).

This is the action I want performed on each database:

select @strResult = definition from sys.sql_modules where object_id = object_id('view')

What do i need to do to ensure that I get this information back?

Best Answer

Need dynamic SQL for this, like in your other answer. Here is my approach:

Build the SQL command you want to run in each database
The IF check in the command ensures you only try to alter a view in a database where that view is found
Generate an execution command like EXEC databasename.sys.sp_executesql so the command doesn't have to be database-aware
Go to town. Cursor vs. while loop is just a preference.
Note that if someone uses tabs, comments, carriage returns etc. before or between CREATE and VIEW when creating the view initially, it becomes more complex to accurately change a CREATE to an ALTER, but the other answer didn't deal with that anyway (and assumed case insensitivity). This is safer because it doesn't assume collation and doesn't replace all instances of create view with alter view (those could be in comments, string literals, etc.)

Good starting point:

DECLARE @view nvarchar(512) = N'[dbo].[viewname]',
        @exec nvarchar(512),
        @db   sysname,
        @sql  nvarchar(max);

SET @sql = N'DECLARE @str nvarchar(max);
  SELECT @str = OBJECT_DEFINITION(OBJECT_ID(@view));
  IF UPPER(LEFT(@str, 11)) = N''CREATE VIEW''
  BEGIN
    SET @str = STUFF(@str, 1, 11, N''ALTER VIEW'');
    SET @str = REPLACE(@str, N''original string'', ''replace string'');
    PRINT DB_NAME();
    PRINT @str;
    --EXEC sys.sp_executesql @str;
  END';

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
  SELECT QUOTENAME(name) FROM sys.databases
  WHERE state = 0 AND database_id > 4;

OPEN c;

FETCH NEXT FROM c INTO @db;
WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @exec = @db + N'.sys.sp_executesql';
  EXEC @exec @sql, N'@view nvarchar(512)', @view;
  FETCH NEXT FROM c INTO @db;
END

CLOSE c; DEALLOCATE c;

When you are happy with the output (note that PRINT will not show everything for a larger view), comment the PRINT statements and uncomment the EXEC). Further reading:

Related Solutions

Sql-server – Create view in a different database with dynamic SQL

This will require really having fun with escaping and re-escaping single quotes, but I think the technique you're after is:

DECLARE 
  @DB  NVARCHAR(255) = QUOTENAME(N'dbname'),
  @SQL NVARCHAR(MAX);

SET @SQL =  N'EXEC ' + @DB + '.sys.sp_executesql '
+ 'N''CREATE VIEW dbo.vWhatever
AS
  SELECT x = 1, y = ''''x'''', z = GETDATE();''';

EXEC sys.sp_executesql @SQL;

So you're kind of nesting the dynamic SQL; the inner one ensures that the SQL is executed at the target database, not locally.

Another way that I've picked up since this original question appeared:

DECLARE 
  @DB  NVARCHAR(255) = QUOTENAME('dbname'),
  @SQL NVARCHAR(MAX),
  @DBExec NVARCHAR(MAX);

SET @DBExec = @DB + N'.sys.sp_executesql';

SET @SQL = N'CREATE VIEW dbo.whatever
  AS
    SELECT x = 1, y = ''x'', z = GETDATE();';

EXEC @DBExec @SQL;

This is slightly tidier because you don't have to double-nest single quotes.

And here is a slightly tidier way to do it without cursors (well, without all the scaffolding of setting up a cursor). Note that the inner dynamic SQL will only be executed in databases where (a) a categories table exists (b) this view does not already exist and (c) it is not a system database (well, not one of the primary system databases, anyway).

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += NCHAR(13) + NCHAR(10)    
  + N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name) 
  + '.sys.views WHERE name = ''vNonhiddenCategories'')
  AND EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  + '.sys.tables WHERE name = ''categories'')
  BEGIN
    EXEC ' + QUOTENAME(name) + '.sys.sp_executesql N''
    CREATE VIEW dbo.vNonhiddenCategories3
    AS
      SELECT x = 1, y = ''''x'''';''
  END'
  FROM sys.databases 
  WHERE database_id BETWEEN 5 AND 32766;

PRINT @sql;
-- EXEC sp_executesql @sql;

Of course "tidier" is in the eye of the beholder.

Sql-server – a good way to replicate code across multiple databases

SQL Server has replication built in. Have you looked into that?

If it's just a few views or procedures, I'll use a Central Management Server to deploy them to multiple servers. You just define the group of servers and then execute the script. Pretty simple.

For 3rd party options, Idera has Multi Query and Red Gate has Multi Script as well.

Best Answer

Related Solutions

Sql-server – Create view in a different database with dynamic SQL

Sql-server – a good way to replicate code across multiple databases

Related Question