Copy Code code as follows:
Use [database name];
--1. Define the keywords you want to find. In search, use fuzzy search: like '% @key_find% '
DECLARE @key_find NVARCHAR (MAX) = ' 123 ';--suppose to find the string "123"
--2. Cursor_table with a cursor, traversing all tables
DECLARE cursor_table Cursor for
SELECT name from sysobjects WHERE xtype = ' u ' and name <> ' dtproperties ';
OPEN cursor_table;
DECLARE @tableName NVARCHAR (MAX);
FETCH NEXT from cursor_table into @tableName;
While @ @fetch_status = 0
BEGIN
DECLARE @tempSQLText NVARCHAR (MAX) = ';
--3. In a table, columncursor with a cursor to iterate through all the fields. Note that only the fields (columns) of the string type are traversed
DECLARE Columncursor CURSOR for
SELECT Name from syscolumns WHERE ID = object_id (@tableName) and
(
Xtype = or--text
Xtype = or--ntext
Xtype = 167 or--varchar
Xtype = 175 or--char
Xtype = 231 or--nvarchar
Xtype = 239 or--nchar
Xtype = 241--xml
)
OPEN Columncursor;
DECLARE @columnName NVARCHAR (MAX);
FETCH NEXT from Columncursor into @columnName;
While @ @fetch_status = 0
BEGIN
--4. In the fields of the table, blur the search for each row and output the information found.
DECLARE @DynamicSQLText NVARCHAR (MAX) = ' IF EXISTS (SELECT * from [' + @tableName + '] WHERE [' + @columnName + '] Like '% ' + @key_find + '% ') BEGIN DECLARE @CurrentTableCount Bigint = (SELECT COUNT (*) from [' + @tableName + ']); PRINT ' find:table [' + @tableName + '], Column [' + @columnName + '], Row Count: ' + CAST (@CurrentTableCount as Nvarch AR (MAX)) + '. '; End ';
EXEC (@DynamicSQLText);
FETCH NEXT from Columncursor into @columnName
End
EXEC (@tempSQLText);
Close columncursor;
Deallocate columncursor;
FETCH NEXT from cursor_table into @tableName;
End
Close cursor_table;
Deallocate cursor_table;
