是否可以搜索 SQL Server 中的所有数据库以获取特定视图?如果是如何?

2021-09-17 00:00:00 sql sql-server information-schema views

我正在尝试查找特定视图.我知道它存在于特定的 SQL Server 上，但我不知道在哪个数据库下.如果我使用 Information_Schema，它只会在当前数据库中搜索.

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%'

是否可以搜索 SQL Server 实例中的所有数据库以获取特定视图?如果是，那么如何?

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

推荐答案

您可以使用 sp_msforeachdb 跨所有数据库检查 sys.views:

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

相关文章