Search all databases on one server instance to find a specific view

Question

Search all databases on one server instance to find a specific view

JP789

SSC Veteran

Points: 214
More actions
March 16, 2025 at 4:48 pm

#4560785

When I log into my instance of SQL Server, there are many databases. I'm trying to find a specific view, but I don't know which database it is in.
I have used
Select * FROM INFORMATION_SCHEMA.VIEWS but that is when I know the database name. We have more than 20 databases in my instance.
Is there a way to search all the databases to find a specific "view"?

Thank you,
JP

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63560 More actions · Answer 1

This may give you an idea... I dumped the result of the query into a temporary table. (Not sure how to parameterize the object name, for some reason.)

CREATE TABLE #DBList(dbname NVARCHAR(50));
GO

DECLARE @command2 VARCHAR(1000)
SELECT @command2 = 'USE ? select DB_NAME(DB_ID(''?'')) FROM sysobjects where xtype = ''V'' AND name LIKE ''vwB%'' ';

INSERT INTO #DBList(dbname)
exec sp_msforeachdb @command2;

SELECT *
FROM #DBList;

pietlinden SSC Guru Points: 63560 More actions · Answer 2

This may give you an idea... I dumped the result of the query into a temporary table. (Not sure how to parameterize the object name, for some reason.)

CREATE TABLE #DBList(dbname NVARCHAR(50));
GO

DECLARE @command2 VARCHAR(1000)
SELECT @command2 = 'USE ? select DB_NAME(DB_ID(''?'')) FROM sysobjects where xtype = ''V'' AND name LIKE ''vwB%'' ';

INSERT INTO #DBList(dbname)
exec sp_msforeachdb @command2;

SELECT *
FROM #DBList;

JP789 SSC Veteran Points: 214 More actions · Answer 3

Humm... I couldn't get it to work. You gave: 'USE ? select DB_NAME(DB_ID(''?''))

But I don't know the database name. The server has many databases. I want to search all the databases in the server to find a specific "view". Please, what am I missing?

Thanks,

JP

pietlinden SSC Guru Points: 63560 More actions · Answer 4

pietlinden

SSC Guru

Points: 63560

March 17, 2025 at 1:59 am

#4560985

the sp_foreachdb part?

Jeff Moden SSC Guru Points: 1004579 More actions · Answer 5

JP789 wrote:

Humm... I couldn't get it to work. You gave: 'USE ? select DB_NAME(DB_ID(''?''))
But I don't know the database name. The server has many databases. I want to search all the databases in the server to find a specific "view". Please, what am I missing?
Thanks,
JP

Make the command work on one database without it being dynamic SQL. I don't believe you'll need anything about the database ID. Once that's done, then do an internet search on how to use the sp_foreachdb undocumented feature.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 247140 More actions · Answer 6

Save yourself the trouble and install Redgate's SQL Search (link).

ratbak Default port Points: 1405 More actions · Answer 7

RedGate SQL Search is an incredibly valuable tool!

ScottPletcher SSC Guru Points: 101187 More actions · Answer 8

DROP TABLE IF EXISTS #view_names;
CREATE TABLE #view_names ( 
    view_name nvarchar(100) PRIMARY KEY
    );
INSERT INTO #view_names
VALUES('>--->YOUR_VIEW_NAME_TO_SEARCH_FOR_GOES_HERE<---<')
/*, ('another_view_name_could_go_here') ... */

DROP TABLE IF EXISTS #is_views;
SELECT TOP (0) *
INTO #is_views
FROM INFORMATION_SCHEMA.VIEWS;

EXEC dbo.sp_MSforeachdb '
USE [?];
INSERT INTO #is_views
SELECT *
FROM INFORMATION_SCHEMA.VIEWS V
WHERE EXISTS(SELECT * FROM #view_names vn WHERE vn.view_name = V.TABLE_NAME)
'

SELECT *
FROM #is_views
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

JP789 SSC Veteran Points: 214 More actions · Answer 9

Thank you, Scott! This works perfectly. I didn't know how to put it all together. Thank you everyone for your help.

JP