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?

  • 1
    You need to make this call dynamic, much like the other statements, and use the databasename.sys.sql_modules, otherwise it references sys.sql_modules of the current database. – Nic Oct 13 '16 at 17:48
  • Let me try that, because at this juncture, that is painfully obvious now that its been pointed out. – Adam Wells Oct 13 '16 at 17:49
  • @nic this doesn't appear to be the magic bullet. Its still returning null. Perhaps it is a permissions issue? – Adam Wells Oct 13 '16 at 18:01

The call needs to be dynamic for the databases, as does the object_id.

This is basic (does not check for offline databases and the like), but gives you an idea as to how it would function, looping through the databases in sys.databases.

DECLARE @viewDef NVARCHAR(max);
DECLARE @DatabaseList sysname;
SELECT @DatabaseList = MIN(name) FROM sys.databases;
WHILE @DatabaseList IS NOT NULL
BEGIN

SELECT @viewDef = 'SELECT Definition from ' + QUOTENAME(@DatabaseList) + '.sys.sql_modules WHERE OBJECT_ID = OBJECT_ID(N'+CHAR(39)+QUOTENAME(@DatabaseList) +'.dbo.view'+CHAR(39)+');'
--PRINT @viewDef
EXEC (@viewDef)

SELECT @DatabaseList = MIN(name) FROM sys.databases WHERE name > @DatabaseList;
END

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:

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.