USE [master]
GO

— exec [SearchTablePrc] ‘TableName’

ALTER PROCEDURE [dbo].[SearchTablePrc]
(
@TableName varchar(500)
)
AS
BEGIN;
SET @TableName = LTRIM(RTRIM(ISNULL(@TableName, ”)));
IF @TableName = ”
RETURN;

DECLARE @DB TABLE(id int identity(1,1), dbName sysname);
DECLARE @RESULTS TABLE(
table_catalog varchar(500),
table_schema varchar(500),
table_name varchar(500));

INSERT INTO @DB
(dbName)
SELECT db.name
FROM sys.databases db;

declare
@min int = 1,
@dbName sysname,
@sql varchar(max);

while @min <= (select MAX(id) from @DB)
begin;
set @dbName = (select dbName from @DB where id = @min);
set @sql = ‘
select table_catalog,table_schema,table_name
from [‘+@dbName+’].INFORMATION_SCHEMA.TABLES
where table_name like ”%’+@TableName+’%”;’;
insert into @RESULTS
exec (@sql);
set @min += 1;
end;

select
table_catalog [Database],
table_schema [Schema],
table_name [Table]
from @RESULTS;
END;