T-SQL——对数据库中的所有表的所有列中搜索给定的值
目录
1. 在指定的表中对其所有列中搜索给定的字符类型的值
这个存储过程有三个参数，第一个是你需要搜索的值，第二个是数据库Owner，第三个参数是指定的表名
返回的结果集包含：搜索值出现的记录的ID，出现的值，出现在的列 等信息
--====================================================================================
--NAME：在指定的表中对其所有列中搜索给定的字符类型的值
--DESC: 适用于搜索Text,NText,Varchar,Nvarchar,Char,NChar等类型的值
--PARAM：@SearchString：需要搜索的字符串
--PARAM：@Table_Schema：数据库所有者(Database Owner)
--PARAM：@Table_Name:需要搜索的表名
--=====================================================================================
ALTER PROCEDURE [dbo].[A_Search_StringInGivenTable] @SearchString NVARCHAR(MAX), @Table_Schema sysname='dbo', @Table_Name sysname
AS BEGIN
DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX);
-- Get all character columns
SET @Columns=STUFF((SELECT ', '+QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text', 'ntext', 'varchar', 'nvarchar', 'char', 'nchar')AND TABLE_NAME=@Table_Name
ORDER BY COLUMN_NAME
FOR XML PATH('')), 1, 2, '');
IF @Columns IS NULL -- no character columns
RETURN -1;
-- Get columns for select statement - we need to convert all columns to nvarchar(max)
SET @Cols=STUFF((SELECT ', cast('+QUOTENAME(COLUMN_NAME)+' as nvarchar(max)) as '+QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text', 'ntext', 'varchar', 'nvarchar', 'char', 'nchar')AND TABLE_NAME=@Table_Name
ORDER BY COLUMN_NAME
FOR XML PATH('')), 1, 2, '');
SET @PkColumn=STUFF((SELECT N' + ''|'' + '+' cast('+QUOTENAME(CU.COLUMN_NAME)+' as nvarchar(max))'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME=CU.TABLE_NAME AND TC.TABLE_SCHEMA=CU.TABLE_SCHEMA AND TC.CONSTRAINT_NAME=CU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY' AND TC.TABLE_SCHEMA=@Table_Schema AND TC.TABLE_NAME=@Table_Name
ORDER BY CU.COLUMN_NAME
FOR XML PATH('')), 1, 9, '');
IF @PkColumn IS NULL SELECT @PkColumn=N'cast(NULL as nvarchar(max))';
-- set select statement using dynamic UNPIVOT
DECLARE @SQL NVARCHAR(MAX);
SET @SQL=N'select *, '+QUOTENAME(@Table_Schema, '''')+N'as [Table Schema], '+QUOTENAME(@Table_Name, '''')+N' as [Table Name]'+N' from
(select ' +@PkColumn+N' as [PK Column], '+@Cols+N' from '+QUOTENAME(@Table_Name)+N' )src UNPIVOT ([Column Value] for [Column Name] IN ('+@Columns+N')) unpvt
WHERE [Column Value] LIKE ''%'' + @SearchString + ''%''';
--print @SQL
EXECUTE sp_executesql @SQL, N'@SearchString nvarchar(max)', @SearchString;
END;
GO
测试
EXECUTE A_Search_StringInGivenTable '张三', 'dbo', 'TargetTableName';
2. 对数据库中的所有表的所有列中搜索给定的字符串类型的值
遍历所有的表，执行上面的搜索存储过程(A_Search_StringInGivenTable)，实现对整个数据库搜索某个特定的字符值
--====================================================================================
--NAME：遍历数据库中的所有表，执行A_Search_StringInGivenTable。
--DESC: 在数据库中所有表搜索某个字符值出现的表及所在的列
--DESC: 适用于搜索Text,NText,Varchar,Nvarchar,Char,NChar等类型的值
--=====================================================================================
ALTER PROC [dbo].[A_Search_String_AllTables](@SearchString NVARCHAR(MAX))
AS BEGIN
CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX),
[COLUMN VALUE] NVARCHAR(MAX),
[COLUMN Name] sysname,
[TABLE SCHEMA] sysname,
[TABLE Name] sysname);
DECLARE @Table_Name sysname, @Table_Schema sysname;
DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;
OPEN curAllTables;
FETCH curAllTables
INTO @Table_Schema, @Table_Name;
WHILE(@@FETCH_STATUS=0) -- Loop through all tables in the database
BEGIN
INSERT #RESULT
EXECUTE A_Search_StringInGivenTable @SearchString, @Table_Schema, @Table_Name;
FETCH curAllTables
INTO @Table_Schema, @Table_Name;
END; -- while
CLOSE curAllTables;
DEALLOCATE curAllTables;
-- Return results
SELECT * FROM #RESULT ORDER BY [TABLE Name];
END;
GO
测试：
DECLARE @return_value int
EXEC @return_value = [dbo].[A_Search_String_AllTables]
@SearchString = N'WantValue'
SELECT 'Return Value' = @return_value
GO
3. 在指定的表中对其所有列中搜索给定的INT类型的值
--====================================================================================
--NAME：在指定的表中对其所有列中搜索给定的INT类型的值
--DESC: 适用于搜索smallint, tinyint, int, bigint等类型
--PARAM：@SearchString：需要搜索的字符串
--PARAM：@Table_Schema：数据库所有者(Database Owner)
--PARAM：@Table_Name:需要搜索的表名
--=====================================================================================
CREATE PROCEDURE [dbo].[A_Search_IntInGivenTable](@SearchValue INT, @Table_Schema sysname, @Table_Name sysname)
AS BEGIN
DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX), @SQL NVARCHAR(MAX);
--判断并创建#Result表
IF OBJECT_ID('TempDB..#Result', 'U') IS NOT NULL DROP TABLE #Result;
CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX),
[COLUMN VALUE] BIGINT,
[COLUMN Name] sysname,
[TABLE SCHEMA] sysname,
[TABLE Name] sysname);
--开始搜索给定的表
DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME=@Table_Name;
OPEN curAllTables;
WHILE 1=1 BEGIN
FETCH curAllTables
INTO @Table_Schema, @Table_Name;
IF @@FETCH_STATUS<>0 -- Loop through all tables in the database
BREAK;
PRINT CHAR(13)+'Processing '+QUOTENAME(@Table_Schema)+'.'+QUOTENAME(@Table_Name);
-- Get all int columns
SET @Columns=STUFF((SELECT ', '+QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%int' AND TABLE_NAME=@Table_Name AND TABLE_SCHEMA=@Table_Schema
ORDER BY COLUMN_NAME
FOR XML PATH('')), 1, 2, '');
IF @Columns IS NULL BEGIN
PRINT 'No int columns in the '+QUOTENAME(@Table_Schema)+'.'+QUOTENAME(@Table_Name);
CONTINUE;
END;
-- Get columns for select statement - we need to convert all columns to bigint
SET @Cols=STUFF((SELECT ', cast('+QUOTENAME(COLUMN_NAME)+' as bigint) as '+QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%int' AND TABLE_NAME=@Table_Name
ORDER BY COLUMN_NAME
FOR XML PATH('')), 1, 2, '');
-- Create PK column(s)
SET @PkColumn=STUFF((SELECT N' + ''|'' + '+' cast('+QUOTENAME(CU.COLUMN_NAME)+' as nvarchar(max))'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME=CU.TABLE_NAME AND TC.TABLE_SCHEMA=CU.TABLE_SCHEMA AND TC.CONSTRAINT_NAME=CU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY' AND TC.TABLE_SCHEMA=@Table_Schema AND TC.TABLE_NAME=@Table_Name
ORDER BY CU.COLUMN_NAME
FOR XML PATH('')), 1, 9, '');
IF @PkColumn IS NULL SELECT @PkColumn=N'cast(NULL as nvarchar(max))';
-- set select statement using dynamic UNPIVOT
SET @SQL=N'select *, '+QUOTENAME(@Table_Schema, '''')+N'as [Table Schema], '+QUOTENAME(@Table_Name, '''')+N' as [Table Name]'+N' from
(select ' +@PkColumn+N' as [PK Column], '+@Cols+N' from '+QUOTENAME(@Table_Schema)+N'.'+QUOTENAME(@Table_Name)+N' )src UNPIVOT ([Column Value] for [Column Name] IN ('+@Columns+N')) unpvt
WHERE [Column Value] = @SearchValue';
--print @SQL -- if we get errors, we may want to print generated SQL
INSERT #RESULT([PK COLUMN], [COLUMN VALUE], [COLUMN Name], [TABLE SCHEMA], [TABLE Name])
EXECUTE sp_executesql @SQL, N'@SearchValue int', @SearchValue;
PRINT 'Found '+CAST(@@ROWCOUNT AS VARCHAR(10))+' records in '+QUOTENAME(@Table_Schema)+'.'+QUOTENAME(@Table_Name);
END;
CLOSE curAllTables;
DEALLOCATE curAllTables;
SELECT * FROM #RESULT ORDER BY [TABLE SCHEMA], [TABLE Name];
END;
4. 对数据库中的所有表的所有列中搜索给定的INT类型的值
--====================================================================================
--NAME：遍历数据库中的所有表，执行A_Search_IntInGivenTable
--DESC: 在数据库中所有表搜索某个字符值出现的表及所在的列
--DESC: 适用于搜索Text,NText,Varchar,Nvarchar,Char,NChar等类型的值
--=====================================================================================
CREATE PROC [dbo].[A_Search_Int_AllTables](@SearchValue INT)
AS BEGIN
CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX),
[COLUMN VALUE] NVARCHAR(MAX),
[COLUMN Name] sysname,
[TABLE SCHEMA] sysname,
[TABLE Name] sysname);
DECLARE @Table_Name sysname, @Table_Schema sysname;
DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;
OPEN curAllTables;
FETCH curAllTables
INTO @Table_Schema, @Table_Name;
WHILE(@@FETCH_STATUS=0) -- Loop through all tables in the database
BEGIN
INSERT #RESULT
EXECUTE A_Search_StringInGivenTable @SearchValue, @Table_Schema, @Table_Name;
FETCH curAllTables
INTO @Table_Schema, @Table_Name;
END; -- while
CLOSE curAllTables;
DEALLOCATE curAllTables;
-- Return results
SELECT * FROM #RESULT ORDER BY [TABLE Name];
END;
测试
DECLARE @return_value INT;
EXEC @return_value=[dbo].[My_Search_Int_AllTables] @SearchValue=68;
SELECT 'Return Value'=@return_value;
4.单独的查询脚本，不区分字段是Int还是String
DECLARE @searchValue NVARCHAR(255) =N'张三'; -- Text or Number (text is converted in number if necessary)
IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL BEGIN
DROP TABLE #SearchResults;
END;
CREATE TABLE #SearchResults (TableName NVARCHAR(128), ColumnName NVARCHAR(128), Value NVARCHAR(MAX));
DECLARE @tableName NVARCHAR(128);
DECLARE @columnName NVARCHAR(128);
DECLARE @query NVARCHAR(MAX);
DECLARE @type NVARCHAR(50);
DECLARE db_cursor CURSOR FOR
SELECT t.name AS TableName, c.name AS ColumnName, ty.name AS Type
FROM sys.tables t
JOIN sys.columns c ON t.object_id=c.object_id
JOIN sys.types ty ON c.user_type_id=ty.user_type_id
WHERE ty.name IN ('nvarchar', 'varchar', 'int', 'bigint', 'smallint', 'tinyint');
OPEN db_cursor;
FETCH NEXT FROM db_cursor
INTO @tableName, @columnName, @type;
WHILE @@FETCH_STATUS=0 BEGIN
IF @type IN ('nvarchar', 'varchar')BEGIN
SET @query=N'INSERT INTO #SearchResults (TableName, ColumnName, Value)
SELECT DISTINCT '''+@tableName+N''', '''+@columnName+N''', '+N'CONVERT(NVARCHAR(MAX), '+@columnName+N') FROM '+@tableName+N' WHERE '+@columnName+N' LIKE ''%'+@searchValue+N'%''';
--PRINT @tableName;PRINT @columnName;--------这里可以将结果打印出来，脚本执行时间过长，可以在执行过程中查看相关的搜索结果集
END;
ELSE IF @type IN ('int', 'bigint', 'smallint', 'tinyint')BEGIN
SET @query=N'INSERT INTO #SearchResults (TableName, ColumnName, Value)
SELECT DISTINCT '''+@tableName+N''', '''+@columnName+N''', '+N'CONVERT(NVARCHAR(MAX), '+@columnName+N') FROM '+@tableName+N' WHERE '+@columnName+N' = TRY_CAST('+QUOTENAME(@searchValue, '''')+N' AS '+@type+N')';
END;
EXEC sp_executesql @query;
FETCH NEXT FROM db_cursor
INTO @tableName, @columnName, @type;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
SELECT * FROM #SearchResults;
DROP TABLE #SearchResults;
