SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

getur.srikanth@gmail.com
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
                            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.
  Previous Topic Topic Next Topic  
 New 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.5 seconds. Powered By: Snitz Forums 2000