0

I have multiple tables (say TableA, TableB, TableC) which have columns to store values in 6 languages. There are thousands of rows in each of these tables in SQL Server 2014.

What is the best way to search for a word or phrase or search string in these three tables (the search string can be found in any column of these tables) ?

Do I create separate views for each language for each of these tables (6 x 3 = 18 views) and then create full-text index on the views ?

I also need to get the table name and column name in which the search string is found in the tables as I need to pass the table name and column name to other stored procedure for further processing.

Create TableA ( 
AID int, 
LanguageID int, 
ACol1 nvarchar(100), 
ACol2 nvarchar(100));

Create TableB ( 
BID int, 
LanguageID int, 
BCol1 nvarchar(100), 
BCol2 nvarchar(100),
BCol3 nvarchar(100));

Create TableC ( 
CID int, 
LanguageID int, 
CCol1 nvarchar(100), 
CCol2 nvarchar(100),
CCol3 nvarchar(100),
CCol4 nvarchar(100));
Bhanu
  • 831
  • 3
  • 13
  • 28

2 Answers2

1
DECLARE @SearchString VARCHAR(200) = 'John'--give search value
DECLARE @IterationTable TABLE   (ID INT IDENTITY(1, 1),
                                Seql VARCHAR(MAX))
DECLARE @StartLoop INT
DECLARE @EndLoop INT

IF OBJECT_ID('tempdb..##Result') IS NOT NULL
    DROP TABLE ##Result

CREATE TABLE ##Result (TableName SYSNAME, ColumnName SYSNAME) 

INSERT INTO @IterationTable
SELECT CONCAT('IF EXISTS(SELECT TOP 1 1 FROM ', QUOTENAME(TABLE_SCHEMA), '.', QUOTENAME(TABLE_NAME), ' WHERE CHARINDEX(''', @SearchString,''', ', QUOTENAME(COLUMN_NAME), ') > 0)', CHAR(13)+CHAR(10), 'BEGIN', CHAR(13)+CHAR(10), 'INSERT INTO ##Result ', CHAR(13)+CHAR(10),'SELECT ''', QUOTENAME(TABLE_NAME),''',''' , QUOTENAME(COLUMN_NAME), '''', CHAR(13)+CHAR(10), 'END')
--SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN('VARCHAR', 'NVARCHAR','CHAR','NCHAR')

SELECT @EndLoop = @@IDENTITY, @StartLoop = 1

--SELECT *,@EndLoop
--FROM @IterationTable

WHILE @StartLoop <= @EndLoop
BEGIN
    DECLARE @SQL VARCHAR(MAX) 

    SELECT @SQL = Seql
    FROM @IterationTable
    WHERE ID = @StartLoop

    EXEC(@SQL);

    PRINT @SQL;

    SET @StartLoop = @StartLoop + 1
END

SELECT * FROM ##Result

--select * from tableC
0

I used the below link as a basis for this requirement.

https://social.technet.microsoft.com/wiki/contents/articles/24169.sql-server-searching-all-columns-in-a-table-for-a-string.aspx

Bhanu
  • 831
  • 3
  • 13
  • 28