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

• JP789

  SSC Veteran

  Points: 209

  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

• pietlinden

  SSC Guru

  Points: 63549

  More actions

  March 16, 2025 at 7:37 pm

  #4560843

  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: 63549

  More actions

  March 16, 2025 at 7:37 pm

  #4560844

  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: 209

  More actions

  March 17, 2025 at 12:15 am

  #4560948

  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: 63549

  More actions

  March 17, 2025 at 1:59 am

  #4560985

  the  sp_foreachdb  part?