Building Search Engine for the Database
In my previous post, How to check the schema of your database through queries, I talked about how to check the schema of the database. In this post, we are going to talk about how to use that to build a Search Engine for the Database, to search in all your database for a specific keyword. The output will be a table with the following fields:
-
Table: The table that contains this keyword
-
Column: The column that contains this keyword
-
Result: The whole value stored in the column that satisfies the search criteria
This will be a stored procedure:
CREATE PROCEDURE [dbo].[SearchAll]
@KeyWord NVARCHAR(MAX)
AS
-- Getting all the columns in the #AllColumns temporary table
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE
INTO #AllColumns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE <> 'image'
AND DATA_TYPE <> 'timestamp'
CREATE TABLE #Result
(
[Table] nvarchar(MAX),
[Column] nvarchar(MAX),
Result nvarchar(MAX)
)
DECLARE @TABLE_SCHEMA NVARCHAR(MAX), -- Temporary variable that holds the TABLE_SCHEMA value
@TABLE_NAME NVARCHAR(MAX), -- Temporary variable that holds the TABLE_NAME value
@COLUMN_NAME NVARCHAR(MAX), -- Temporary variable that holds the COLUMN_NAME value
@DATA_TYPE NVARCHAR(MAX), -- Temporary variable that holds the DATA_TYPE value
@RESULT NVARCHAR(MAX), -- Temporary variable that holds the value in the column that contains the keyword
@Query NVARCHAR(MAX) -- The query of the search
-- Looping in each column (Same functionality of the cursor, but better performance)
WHILE EXISTS(SELECT TABLE_SCHEMA FROM #AllColumns)
BEGIN
-- Getting single row in the #AllColumns table
SELECT @TABLE_SCHEMA = TABLE_SCHEMA,
@TABLE_NAME = TABLE_NAME,
@COLUMN_NAME = COLUMN_NAME,
@DATA_TYPE = DATA_TYPE
FROM #AllColumns
-- Building the insert query to the #Result table if the keyword exists in the column @COLUMN_NAME
SET @Query = 'INSERT INTO #Result([Table], [Column], Result) '
+ 'SELECT ''' + @TABLE_NAME + ''', '
+ '''' + @COLUMN_NAME + ''', '
+ 'CONVERT(nvarchar(MAX), [' + @COLUMN_NAME + ']) '
+ 'FROM [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + '] '
IF @DATA_TYPE = 'xml' OR @DATA_TYPE = 'sql_variant'
BEGIN
SET @Query = @Query + 'WHERE CONVERT(nvarchar(MAX), [' + @COLUMN_NAME + ']) LIKE ''%' + @KeyWord + '%'''
END
ELSE
SET @Query = @Query + 'WHERE [' + @COLUMN_NAME + '] LIKE ''%' + @KeyWord + '%'''
--Execute the query
EXEC sp_executesql @Query
-- Delete the selected row from the #AllColumns table
DELETE FROM #AllColumns
WHERE TABLE_SCHEMA = @TABLE_SCHEMA
AND TABLE_NAME = @TABLE_NAME
AND COLUMN_NAME = @COLUMN_NAME
END
-- Displaying the result
SELECT *
FROM #Result