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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Search and retrieving word(s) from multiple tables
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic
Yak Posting Veteran

77 Posts

Posted - 02/07/2013 :  14:07:25  Show Profile  Reply with Quote
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
                         End If
                      End If
                   End If
            End If

Stored Procedure

    ALTER PROCEDURE [dbo].[sp_FindStringInTable] @stringToFind VARCHAR(100), @schema sysname, @table sysname, @dbname varchar(100)
    DECLARE @sqlCommand NVARCHAR(Max) 
    DECLARE @where NVARCHAR(Max)
    DECLARE @columnName sysname 
    DECLARE @cursor VARCHAR(8000) 
       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 
       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    
           IF @where <> '' 
               SET @where = @where + ' OR'
           SET @where = @where + ' [' + @columnName + '] LIKE ''%' + @stringToFind + '%''' 
           FETCH NEXT FROM col_cursor INTO @columnName 
           PRINT @columnName   
       CLOSE col_cursor    
       DEALLOCATE col_cursor  
       SET @sqlCommand = @sqlCommand + @where 
       PRINT @sqlCommand 
        IF @where <> '' 
    		EXEC (@sqlCommand)  
    END TRY 
            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 
           CLOSE col_cursor    
           DEALLOCATE col_cursor  

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.
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000