sp_FindTablesIncludeSynonyms - finds all tables given a wildcard in the current database and its synonyms.

sp_FindTablesIncludeSynonyms.sql

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_FindTablesIncludeSynonyms]    Script Date: 05/17/2013 08:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_FindTablesIncludeSynonyms]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_FindTablesIncludeSynonyms]
GO

CREATE Procedure [dbo].[sp_FindTablesIncludeSynonyms]
  @TableName varchar(max)
AS

DECLARE @sql VARCHAR(MAX), @ServerName VARCHAR(MAX), @dbName VARCHAR(MAX)

SET @sql = 'SELECT ''local'' as server, table_catalog, table_schema, table_name, Table_Type, 0 as synonym ' +
           'FROM   information_schema.tables '+
	       'WHERE  table_name LIKE ''%'+@TableName+'%''';

DECLARE TableCursor CURSOR FOR

SELECT DISTINCT
SUBSTRING(COALESCE(PARSENAME(base_object_name,4),@@servername), 
	  CHARINDEX('.', COALESCE(PARSENAME(base_object_name,4),@@servername)) + 1, 
	  LEN(COALESCE(PARSENAME(base_object_name,4),@@servername))) as ServerName,
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName
FROM sys.synonyms 
WHERE COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) <> DB_NAME()
ORDER BY serverName,dbName

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @ServerName, @dbName

WHILE @@FETCH_STATUS = 0
BEGIN

   set @sql = @sql + 
   'UNION ' +
   'SELECT '''+@ServerName+''' as server, table_catalog, table_schema, table_name, table_type, 1 as synonym ' +
   'FROM   ['+@ServerName+'].['+@dbName+'].Information_schema.tables ' +
   'WHERE  table_name LIKE ''%'+@TableName+'%'' '
   
   FETCH NEXT FROM TableCursor INTO @ServerName, @dbName
END

set @sql = @sql + 'ORDER BY synonym, server, table_schema, table_name;'
CLOSE TableCursor
DEALLOCATE TableCursor

exec (@sql)
GO

EXEC sys.sp_MS_marksystemobject [sp_FindTablesIncludeSynonyms]
GO