APPLY GOOGLE LIKE SEARCH STORED PROCEDURE

Hi..

Today I m posting a very good as well as very important Stored procedure which is used to apply the google type search in your data base here in this SP i am giving the IDs of the table in which i want to search.
Please have a look its very intersting..


CREATE PROCEDURE PROC_Google_Like_Search
(
@InputText varchar(100)
)
AS
BEGIN
SET NOCOUNT ON;

declare @SearchChar varchar(8000)
Set @SearchChar = '%'+ @InputText+ '%'--'%partner%'--, '11/11/2006'

declare @CMDMain varchar(8000), @CMDMainCount varchar(8000),@CMDJoin varchar(8000)
declare @ColumnName varchar(100),@TableName varchar(100)

declare dbTable cursor for
SELECT
Distinct b.Name as TableName
FROM
sysobjects b
WHERE id in ( 1195151303,1115151018,859150106) --*** see The Note Comment for this

-- b.type='u' and b.Name <> 'dtproperties'
--order by b.name
open dbTable
fetch next from dbTable into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
declare db cursor for
SELECT
c.Name as ColumnName
FROM
sysobjects b,
syscolumns c
WHERE
C.id = b.id and
b.type='u' and b.Name = @TableName
order by b.name

open db
fetch next from db into @ColumnName

set @CMDMain = 'SELECT ' + char(39) + @TableName + char(39) + ' as TableName,'+
' ['+ @TableName + '].* FROM [' + @TableName + ']'+
' WHERE '
set @CMDMainCount = 'SELECT Count(*) FROM [' + @TableName + '] Where '
Set @CMDJoin = ''

WHILE @@FETCH_STATUS = 0
BEGIN

set @CMDJoin = @CMDJoin + 'Convert(varchar(5000),[' +@ColumnName + ']) like ' + char(39) + @SearchChar + char(39) + ' OR '

fetch next from db into @ColumnName
end
close db
deallocate db

Set @CMDMainCount = 'If ('+ @CMDMainCount + Left(@CMDJoin, len(@CMDJoin) - 3)+ ') > 0 Begin '
Set @CMDMain = @CMDMainCount + @CMDMain + Left(@CMDJoin, len(@CMDJoin) - 3)
Set @CMDMain = @CMDMain + ' End '

Print @CMDMain

exec (@CMDMain)

fetch next from dbTable into @TableName
end
close dbTable
deallocate dbTable
END



***NOTE :-
To get the table id to be searched from the sysObject..
Select * from sysobjects where xtype='u' order by crdate desc



Thanks ...
Sanjeev Chauhan
HelpOnDesk Team