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

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.