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';