Wednesday, June 17, 2015

Find Table in Multiple Databases


http://stackoverflow.com/questions/610609/find-a-table-across-multiple-databases-sql-server-2005

sp_MSforeachdb 'USE ?
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[table_name_here]'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
  PRINT ''Found in db ?''
END'

Or ...

USE master 
GO 
SELECT 'USE [' + name + '] ' + CHAR (13) + CHAR (10) 
+ 'GO ' + CHAR (13) + CHAR (10) 
+ 'SET NOCOUNT ON; SELECT DB_NAME(); SELECT name FROM sysobjects WHERE type = ''U'' AND  name = ''some_table'' '
+ CHAR (13) 
FROM sysdatabases 

No comments: