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 NextNext
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.