Global DB Search Util

I've written a small search util that displays all tables/columns that
contain the given search string.
EXEC usp_GlobalFindText 'MyText'
The problem is that EXEC(@.STR) can only return a @.@.ERROR code, I Tried:
RETURN EXEC(@.SQL) --@.SQL = SELECT COUNT(*) FROM Table WHERE ID=5
This always returns 0 because no error is thrown...@.@.ERROR = 0
Is there a more elegant way search every table/Text column?
--Billg_sd
--SOURCE CODE--
CREATE PROCEDURE dbo.usp_GetRowResult
(
@.ROWID INT,
@.VAL INT OUTPUT
)
AS
DECLARE @.SQL VARCHAR(180)
SELECT @.SQL = [Query] FROM SEARCH WHERE RowID = @.ROWID
EXEC(@.SQL)
IF @.@.ERROR <> 0
SET @.VAL = 1
ELSE
SET @.VAL = 0
RETURN
GO
CREATE PROCEDURE dbo.usp_GlobalFindText
(
@.PATTERN VARCHAR(64)
)
/* WITH ENCRYPTION */
AS
SET NOCOUNT ON
DECLARE @.ICOUNT INT
DECLARE @.TOPROWINDEX INT
DECLARE @.RETVAL INT
DECLARE @.ROWID INT
--DECLARE @.PATTERN VARCHAR(64)
--EXTRACT TEXT FIELDS TO SEARCH
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SEARCH
')
DROP TABLE SEARCH
SELECT
IDENTITY(INT, 1, 1) AS RowID,
TABLE_NAME,
COLUMN_NAME,
QUERY = 'IF EXISTS(SELECT * FROM ' + TABLE_NAME +
' WHERE ' + COLUMN_NAME + ' LIKE ' + char(39) + '%' + @.PATTERN
+ '%' + char(39) + ') RAISERROR('''',11,1) ' ,
CAST(0 AS INT) AS TOTAL
INTO SEARCH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('char','nvarchar','ntext','varchar', 'text')
ORDER BY TABLE_NAME, COLUMN_NAME
--SEARCH FIELDS
SELECT @.ICOUNT = MIN(ROWID) FROM SEARCH
SELECT @.TOPROWINDEX = MAX(ROWID) FROM SEARCH
WHILE @.ICOUNT <= @.TOPROWINDEX
BEGIN
SELECT @.ROWID = ROWID FROM SEARCH WHERE ROWID = @.ICOUNT
IF @.ICOUNT IS NULL
CONTINUE
EXEC usp_GetRowResult @.ROWID, @.RETVAL OUTPUT
--PRINT '@.RETVAL = ' + CAST(@.RETVAL AS VARCHAR(10))
UPDATE SEARCH
SET TOTAL = @.RETVAL
WHERE RowID = @.ROWID
SET @.ICOUNT = @.ICOUNT + 1
IF @.ICOUNT <= @.TOPROWINDEX
CONTINUE
ELSE
BREAK
END
--Display results
SELECT TABLE_NAME, COLUMN_NAME FROM SEARCH WHERE TOTAL <> 0
GOVyas has a great SP on his website
http://vyaskn.tripod.com/
"Billg_sd" <Billgsd@.discussions.microsoft.com> wrote in message
news:CDF6DF52-2341-4377-A737-7FC4CFC75E31@.microsoft.com...
> I've written a small search util that displays all tables/columns that
> contain the given search string.
> EXEC usp_GlobalFindText 'MyText'
> The problem is that EXEC(@.STR) can only return a @.@.ERROR code, I Tried:
> RETURN EXEC(@.SQL) --@.SQL = SELECT COUNT(*) FROM Table WHERE ID=5
> This always returns 0 because no error is thrown...@.@.ERROR = 0
> Is there a more elegant way search every table/Text column?
> --Billg_sd
> --SOURCE CODE--
> CREATE PROCEDURE dbo.usp_GetRowResult
> (
> @.ROWID INT,
> @.VAL INT OUTPUT
> )
> AS
> DECLARE @.SQL VARCHAR(180)
> SELECT @.SQL = [Query] FROM SEARCH WHERE RowID = @.ROWID
> EXEC(@.SQL)
> IF @.@.ERROR <> 0
> SET @.VAL = 1
> ELSE
> SET @.VAL = 0
> RETURN
> GO
> CREATE PROCEDURE dbo.usp_GlobalFindText
> (
> @.PATTERN VARCHAR(64)
> )
> /* WITH ENCRYPTION */
> AS
> SET NOCOUNT ON
> DECLARE @.ICOUNT INT
> DECLARE @.TOPROWINDEX INT
> DECLARE @.RETVAL INT
> DECLARE @.ROWID INT
> --DECLARE @.PATTERN VARCHAR(64)
> --EXTRACT TEXT FIELDS TO SEARCH
> IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'SEARCH')
> DROP TABLE SEARCH
> SELECT
> IDENTITY(INT, 1, 1) AS RowID,
> TABLE_NAME,
> COLUMN_NAME,
> QUERY = 'IF EXISTS(SELECT * FROM ' + TABLE_NAME +
> ' WHERE ' + COLUMN_NAME + ' LIKE ' + char(39) + '%' + @.PATTERN
> + '%' + char(39) + ') RAISERROR('''',11,1) ' ,
> CAST(0 AS INT) AS TOTAL
> INTO SEARCH
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE IN ('char','nvarchar','ntext','varchar', 'text')
> ORDER BY TABLE_NAME, COLUMN_NAME
> --SEARCH FIELDS
> SELECT @.ICOUNT = MIN(ROWID) FROM SEARCH
> SELECT @.TOPROWINDEX = MAX(ROWID) FROM SEARCH
> WHILE @.ICOUNT <= @.TOPROWINDEX
> BEGIN
> SELECT @.ROWID = ROWID FROM SEARCH WHERE ROWID = @.ICOUNT
> IF @.ICOUNT IS NULL
> CONTINUE
> EXEC usp_GetRowResult @.ROWID, @.RETVAL OUTPUT
> --PRINT '@.RETVAL = ' + CAST(@.RETVAL AS VARCHAR(10))
> UPDATE SEARCH
> SET TOTAL = @.RETVAL
> WHERE RowID = @.ROWID
> SET @.ICOUNT = @.ICOUNT + 1
> IF @.ICOUNT <= @.TOPROWINDEX
> CONTINUE
> ELSE
> BREAK
> END
> --Display results
> SELECT TABLE_NAME, COLUMN_NAME FROM SEARCH WHERE TOTAL <> 0
> GO
>
>|||This website is huge. Where can I find this sample?
"Uri Dimant" wrote:

> Vyas has a great SP on his website
> http://vyaskn.tripod.com/
>