CODEWITHSUNDEEP
sqlsql-serverviewinformation-schema
Anjani Kumar Agrawal
Anjani Kumar Agrawal

Reputation: 375

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

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?

Upvotes: 1

Views: 1366

Answers (3)

marc_s
marc_s

Reputation: 754518

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

enter image description here

enter image description here

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

Upvotes: 3

GuidoG
GuidoG

Reputation: 12014

you can build a list to search for the view in each database like this 

SELECT 'select * from ' + name + '.INFORMATION_SCHEMA.Views v where  v.Table_Name like ''%vwViewName%''' 
FROM   master.dbo.sysdatabases

this will return a result like this for example

select * from master.INFORMATION_SCHEMA.Views v where  v.Table_Name like '%vwViewName%' 
select * from tempdb.INFORMATION_SCHEMA.Views v where  v.Table_Name like '%vwViewName%' 
select * from model.INFORMATION_SCHEMA.Views v where  v.Table_Name like '%vwViewName%'  
select * from msdb.INFORMATION_SCHEMA.Views v where  v.Table_Name like '%vwViewName%'   
select * from Test.INFORMATION_SCHEMA.Views v where  v.Table_Name like '%vwViewName%'   
select * from Prod.INFORMATION_SCHEMA.Views v where  v.Table_Name like '%vwViewName%'   
select * from sysdb.INFORMATION_SCHEMA.Views v where  v.Table_Name like '%vwViewName%'

Then you can run each of these queries

you can offcourse filter out tempdb and other databases.
Also, if you want only run one query then use union like this 

select table_catalog + '.' + table_schema + '.' + table_name  from test.INFORMATION_SCHEMA.Views v where  v.Table_Name like '%vwViewName%'
union all   
select table_catalog + '.' + table_schema + '.' + table_name  from prod.INFORMATION_SCHEMA.Views v where  v.Table_Name like '%vwViewName%'

now you have only one query to run and still get all databases where the view is found and the result will be in format DBName.dbo.ViewName

Upvotes: 1

Horia
Horia

Reputation: 1612

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

Upvotes: 3

Related Questions