Is it possible to search all databases in a SQL Server for a specific view? If yes how?

Question

I am trying to find a specific view. I know it exists on a specific SQL Server but I don't know under which Database. If I use Information_Schema it would only search within the current Database.

SELECT * 
FROM INFORMATION_SCHEMA.Views v 
WHERE v.Table_Name LIKE '%vwViewName%'

Is it possible to search all databases in a SQL Server instance for a specific view? If yes, then how?

Answer 1

You can use sp_msforeachdb to check sys.views across all databases:

    DECLARE @views TABLE (ViewName NVARCHAR(4000))
    DECLARE @ViewName NVARCHAR(4000) = 'ViewName'
    DECLARE @query NVARCHAR(4000) = 'SELECT ''?'' + ''.'' + s.name + ''.'' + v.name from [?].sys.views v inner join sys.schemas s on v.schema_id=s.schema_id WHERE v.name LIKE ''%' + @ViewName + '%'''

    INSERT INTO @views (ViewName)
    EXEC sp_msforeachdb @query

    SELECT  * 
    FROM    @views