[SQLSERVER] Hướng dẫn viết procedure tìm chuỗi trong tất cả các cột của table

Bài viết hôm nay, mình sẽ tiếp tục chia sẽ cho các bạn một procedure tìm kiếm chuỗi trong tất cả các cột của một table trong sqlserver.

Ví dụ: Các bạn có một bảng table tbl_sinhvien(masv, tensv, diachi, ngaysinh, noisinh, tuoi).

Bây giờ, nếu các bạn muốn tìm chuỗi "lap trinh" trong bảng tbl_sinhvien. 

Các bạn sẽ thực hiện câu lệnh sql sau:

SELECT * FROM tbl_sinhvien WHERE masv like '%lap trinh%' or tensv like '%lap trinh%' or diachi like '%lap trinh%' or ngaysinh like '%lap trinh%' or noisinh like '%lap trinh%' or tuoi like '%lap trinh%'

Cách 1: Sử dụng Cursor

USE master 
GO 

CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname 
AS 

DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @cursor VARCHAR(8000) 

BEGIN TRY 
   SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE' 
   SET @where = ' 

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME 
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = ' + @schema + ' 
   AND TABLE_NAME = ' + @table + ' 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')' 

   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    
   END    

   CLOSE col_cursor    
   DEALLOCATE col_cursor  

   SET @sqlCommand = @sqlCommand + @where 
   PRINT @sqlCommand 
   EXEC (@sqlCommand)  
END TRY 
BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.'
   PRINT error_message()
    
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3 
   BEGIN 
       CLOSE col_cursor    
       DEALLOCATE col_cursor  
   END 
END CATCH 

Cách 2: không sử dụng cursor

USE master
GO

CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname 
AS

BEGIN TRY
   DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE ' 
	   
   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ' + @stringToFind + ' OR '
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = @schema
   AND TABLE_NAME = @table 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

   SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
   EXEC (@sqlCommand)
   PRINT @sqlCommand
END TRY

BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.'
   PRINT error_message()
END CATCH 

* Bây giờ, mình sẽ thực hiện câu lệnh ví dụ như sau:

USE AdventureWorks 
GO 
EXEC sp_FindStringInTable '%land%', 'Person', 'Address'

==> Kết quả trả về

HAVE FUN :)