Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Search and retrieving word(s) from multiple tables

Author  Topic 

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2013-02-07 : 14:07:25
User is passing searching keywords to one or more databases and finding each word in multiple tables and count number of rows where word is found.

So What I did is here.. I was looping in each word from word array and password this word to each table and retrieving row count. Now I am getting a time out error here while searching multiple keywords and 590 in tables. because for looping keywords and calling tables multiple times. See my code.

VB.NET code

For Each keyword As String In keywords
For Each lstOfSelectedTable As String In lstOfSelectedTables
Dim words As List(Of String) = lstOfSelectedTable.Split("."c).ToList()
If words.Count > 1 Then
dbName = words(0)
schema = words(1)
tableName = words(2)
If strKeywords.Length > 0 Then
dtExcel = FillData(keyword, schema, tableName, serverName, dbName)
If dtExcel.Rows.Count > 0 Then
If CType(dtExcel.Rows(0).Item(3), Integer) > 0 Then
ds.Tables.Add(dtExcel)
End If
End If

End If
End If
Next
Next

Stored Procedure

ALTER PROCEDURE [dbo].[sp_FindStringInTable] @stringToFind VARCHAR(100), @schema sysname, @table sysname, @dbname varchar(100)
AS


DECLARE @sqlCommand NVARCHAR(Max)
DECLARE @where NVARCHAR(Max)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(8000)


BEGIN TRY
SET @sqlCommand = 'SELECT '''+ @stringToFind +''' as ''Keyword'','''+@dbname +''' As ''Database'', '''+ @table +''' as ''Table'',count(*) as Count FROM [' + @dbname + '].[' + @schema + '].[' + @table + '] WHERE'
SET @where = ''


SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
FROM ' + @dbname + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''' + @schema + '''
AND TABLE_NAME = ''' + @table + '''
AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'


PRINT @cursor
EXEC (@cursor)

OPEN col_cursor
FETCH NEXT FROM col_cursor INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN
IF @where <> ''
SET @where = @where + ' OR'

SET @where = @where + ' [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''
FETCH NEXT FROM col_cursor INTO @columnName
PRINT @columnName
END


CLOSE col_cursor
DEALLOCATE col_cursor


SET @sqlCommand = @sqlCommand + @where
PRINT @sqlCommand
IF @where <> ''
EXEC (@sqlCommand)
ELSE
RETURN

END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
PRINT 'There was an error. Check to make sure object exists.'
IF CURSOR_STATUS('variable', 'col_cursor') <> -3
BEGIN
CLOSE col_cursor
DEALLOCATE col_cursor
END
END CATCH

I am passing like this.

exec sp_FindStringInTable 'Application','dbo','BAK_tbl_Timeline_032112','Sui_WIP'
exec sp_FindStringInTable 'Application','dbo','tbl_SampleList','Sui_WIP'
exec sp_FindStringInTable 'Insurance','dbo','BAK_tbl_Timeline_032112','Sui_WIP'
exec sp_FindStringInTable 'Insurance','dbo','tbl_SampleList','Sui_WIP'
exec sp_FindStringInTable 'Reduced','dbo','BAK_tbl_Timeline_032112','Sui_WIP'
exec sp_FindStringInTable 'Reduced','dbo','tbl_SampleList','Sui_WIP'

Continually for around 600 tables. Now I want optimize store procedure.


exec sp_FindStringInTable 'Application;Insurance;Reduced','dbo','BAK_tbl_Timeline_032112','Sui_WIP'
exec sp_FindStringInTable 'Application;Insurance;Reduced','Person','Person','AdventureWorks'


Please help me out changing this sp and run efficiently.

NOTE: User can pass multilple words by selecting multiple DBs (or) All Databases in the server and Selecting multiple Tables (or) All Tables on databases.
   

- Advertisement -