How to select sys.views of another database from master databse?

Question

I know I can get views metadata using sys.views system catalog view. But now I want to create a centralized function in my master database to be called from other databases.

For example, I want to create a procedure, to refresh all views inside a database:

select 'execute sp_refreshview ''' + object_name([object_id], db_id()) + ''''
from sys.views 

I know that I can pass db_id to the object_name for example, to retrieve data from another database. But how can I tell SQL Server that it should use the sys.views of another database?

Answer 1

Basically you need nested dynamic SQL to achieve the requirement as stated. If we are sure the database exists, that it's online, and the user has access, we can build a command to execute sp_executesql in the specified database, as Erland Sommarskog describes here, which in turn runs dynamic SQL to generate the list of refresh commands in that database:

CREATE OR ALTER PROCEDURE dbo.RefreshAllViewsInDatabase
  @dbname sysname
AS
BEGIN
  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)    -- 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 @exec @outerSQL;
END

Sample call:

EXEC dbo.RefreshAllViewsInDatabase @dbname = N'msdb';

For me this yielded something like this (and executed the commands in msdb):

EXEC sys.sp_refreshsqlmodule N'[dbo].[syspolicy_policy_category_subscriptions]';
EXEC sys.sp_refreshsqlmodule N'[dbo].[sysutility_ucp_computer_cpu_health]';
EXEC sys.sp_refreshsqlmodule N'[dbo].[sysutility_ucp_mi_volume_space_health]';
...

I blogged about this in more detail as the start of a new series:

• "I want to do X to all the Ys in database Z" – Part 1

Answer 2

To query other databases sys.views add the database name before it,

SELECT * FROM master.sys.views
SELECT * FROM AdventureWorks.sys.views
SELECT * FROM WideWorldImporters.sys.views

We call it a three-part naming scheme or as in MS DOCS multipart names, you can refer this here

So your code becomes like below,

select * FROM yourdbname.sys.views

To generate all the databases as a dynamic query

SELECT 'SELECT * FROM '+convert(varchar(120),DB_NAME(database_id))+'.sys.views' 
  FROM sys.databases

Now it's up to you that to create a function or stored procedure according to your need. if your objective is to get all views from all databases and to do execute sp_refreshview then you have to create a dynamic SQL to pull all the data from each database execute it dynamically.

Answer 3

if you selection of view from another server and database so please see below Query

select * from [Link Server].[Database Name].[sys].[view]