USE master;
GO
CREATE OR ALTER PROCEDURE dbo.sp__SEARCH_TABLE_ALL_SERVER 
       @SCHEMA NVARCHAR(128), 
       @TABLE  NVARCHAR(128),
       @JOKER  BIT,
       @ESCAPE NCHAR(1)
AS
/******************************************************************************
* MODULE : META                                                               *
* NATURE : PROCEDURE                                                          *
* OBJECT : master.dbo.sp__SEARCH_TABLE_ALL_SERVER                             *
* CREATE : 2018-05-28                                                         *
* VERSION : 1                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://mssqlserver.fr           website: http://sqlpro.developpez.com *
* Most Valuable Professional  - MVP -  SQL Server (data platform) depuis 2004 *
*******************************************************************************
* PURPOSE : search a user's table in all the databases of an instance         *
*******************************************************************************
* INPUTS :                                                                    *
*    @SCHEMA NVARCHAR(128) the name of the table's schema in which we search  *
*    @TABLE  NVARCHAR(128) the name of the table to be search                 *
*    @JOKER  BIT           if 1 use LIKE and accepts LIKE's jokers            *
*    @ESCAPE NCHAR(1)      nchar for escpaping from LIKE                      *
*******************************************************************************
* OUPUT : table as describe :                                                 *
*    TABLE_CATALOG sysname      the database name                             *
*    TABLE_SCHEMA sysname       the table schema name                         *
*    TABLE_NAME sysname         the table name                                *
*    TABLE_TYPE varchar(10)     the table type (BASE TABLE or VIEW)           *
*******************************************************************************
* RUNNABLE : version 2008 and superior                                        *
*******************************************************************************
* SAMPLES :                                                                   *
*     EXEC sp__SEARCH_TABLE_ALL_SERVER  NULL,  'LOT',   NULL, NULL            *
*     EXEC sp__SEARCH_TABLE_ALL_SERVER  'dbo', 'LOT',   NULL, NULL            *
*     EXEC sp__SEARCH_TABLE_ALL_SERVER  NULL,  'LOT%',  1,    NULL            *
*     EXEC sp__SEARCH_TABLE_ALL_SERVER  NULL,  'T?_%',  1,    '?'             *
******************************************************************************/
DECLARE @SQL NVARCHAR(max);
SET @SQL = N''; 
SET  @SCHEMA = COALESCE('''' + @SCHEMA + '''', 'NULL');
SELECT @SQL = @SQL 
       + N'SELECT TABLE_CATALOG COLLATE SQL_Latin1_General_CP850_BIN AS [DATABASE_NAME], '
       + N'TABLE_SCHEMA COLLATE SQL_Latin1_General_CP850_BIN AS TABLE_SCHEMA, '
       + N'TABLE_NAME COLLATE SQL_Latin1_General_CP850_BIN AS TABLE_NAME, '
       + N'TABLE_TYPE COLLATE SQL_Latin1_General_CP850_BIN AS TABLE_NAME FROM [' 
       + name + N'].INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA '
       + CASE @JOKER WHEN 1 
              THEN N'LIKE ' 
              ELSE N'= '
         END + N' COALESCE(' + @SCHEMA + N', TABLE_SCHEMA) '
       + CASE WHEN @JOKER = 1 AND @ESCAPE IS NOT NULL 
              THEN 'ESCAPE ''' + @ESCAPE + ''''
              ELSE N'' END + N' AND TABLE_NAME '
       + CASE @JOKER WHEN 1 
              THEN N'LIKE ' 
              ELSE N'= '
         END + '''' + @TABLE + N''' '
       + CASE WHEN @JOKER = 1 AND @ESCAPE IS NOT NULL 
              THEN 'ESCAPE ''' + @ESCAPE + ''''
              ELSE N'' END + N' UNION ALL ' 
FROM   sys.databases; 
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);
PRINT @SQL
EXEC (@SQL);
GO

EXEC sp_MS_marksystemobject 'sp__SEARCH_TABLE_ALL_SERVER';