CREATE	PROC [dbo].[SearchDb]
	@DbName sysname,
	@SearchArg varchar(2000)
AS
BEGIN
	SET NOCOUNT ON
	CREATE  TABLE ##SearchResults(
		   SPID int
		 , SearchDate datetime
		 , TABLE_CATALOG sysname
		 , TABLE_SCHEMA sysname
		 , TABLE_NAME sysname
		 , COLUMN_NAME varchar(255)
		 , DATA_TYPE varchar(25)
		 , SEARCHARG varchar(2000)
		 , RESULTS varchar(4000))

	DECLARE @sql varchar(8000), @Date datetime
	DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, 
		@COLUMN_NAME sysname, @DATA_TYPE varchar(25)

	select @Date = getdate()

	exec('DECLARE SearchCursor CURSOR FOR
	SELECT 	c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
	FROM 	' + @DbName + '.INFORMATION_SCHEMA.Columns c 
		JOIN ' + @DbName + '.INFORMATION_SCHEMA.Tables t ON c.TABLE_CATALOG = t.TABLE_CATALOG
							AND c.TABLE_SCHEMA  = t.TABLE_SCHEMA
							AND c. TABLE_NAME   = t.TABLE_NAME
	WHERE 	c.DATA_TYPE IN (''char'', ''nchar'', ''varchar'', ''nvarchar'', ''text'', ''ntext'' , 
		''datetime'', ''uniqueidentifier'') 
		and t.TABLE_TYPE = ''BASE TABLE''')

	OPEN SearchCursor
	FETCH NEXT FROM SearchCursor INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @sql = 'SET NOCOUNT ON ' + CHAR(13) 
		+ ' INSERT INTO ##SearchResults(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, ' + 
		+ 'TABLE_NAME,COLUMN_NAME,DATA_TYPE,SEARCHARG,RESULTS) '
		+ 'SELECT ' + CONVERT(varchar(15),@@SPID) + ',' + '''' 
		+ CONVERT(varchar(25),@Date,120) + '''' + ',' + '''' +
		+ @TABLE_CATALOG + ''',''' + @TABLE_SCHEMA + ''',''' +  @TABLE_NAME + ''',' + '''' 
		+ @COLUMN_NAME + ''',' + '''' + @DATA_TYPE + ''',' + ''''+ @SearchArg + '''' + ',' 
		+ 'CONVERT(varchar(4000),' + '[' + @COLUMN_NAME + ']' + CASE WHEN @DATA_TYPE = 'datetime' THEN ', 121' else '' end + ')'
		+ ' FROM ' + '['+ @TABLE_CATALOG + '].[' + @TABLE_SCHEMA + '].[' +  @TABLE_NAME + '] '
		+ ' WHERE PATINDEX( ' + '''' + '%' + @SearchArg + '%' + '''' + ',' 
		+ CASE WHEN @DATA_TYPE = 'datetime' or @DATA_TYPE = 'uniqueidentifier' THEN 'CONVERT(varchar(100),' + '[' + @COLUMN_NAME + ']'
		+ ', 121)' ELSE '[' + @COLUMN_NAME + ']' END 
		+ ') <> 0'

		--SELECT @sql
		EXEC(@sql)
		FETCH NEXT FROM SearchCursor INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE
	END
	SET NOCOUNT OFF

	SELECT	distinct SEARCHARG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, RESULTS , TABLE_CATALOG, 
		SearchDate, SPID, TABLE_SCHEMA
	FROM    ##SearchResults
	WHERE 	SPID = @@SPID and COLUMN_NAME is not null
	ORDER BY TABLE_NAME, COLUMN_NAME
	
	CLOSE SearchCursor
	DEALLOCATE SearchCursor  	
	
	DROP TABLE ##SearchResults
END