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?


3 Answers 3


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
  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
    RAISERROR('Database %s is not available.', 11, 1, @dbname);

  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;

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:


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.


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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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