Home > Database > Mysql Tutorial > sqlserver中在指定数据库的所有表的所有列中搜索给定的值

sqlserver中在指定数据库的所有表的所有列中搜索给定的值

WBOY
Release: 2016-06-07 18:02:51
Original
809 people have browsed it

最近因ERP项目,我们需要知道前台数据导入功能Application操作的导入字段都写入到了后台数据库哪些表的哪些列

比如:我们导入了某个客户的资料,我们知道此客户的姓名是ZhangShan,我们想知道,在我们的业务数据库(eg:NorthWind)中,有哪些数据表的哪些字段设置了此姓名值ZhangShan,通过下面的SQL,我们就可以实现此目的,此处的SQL搜索自网上,在此处做了局部修改。
一、搜索数据是String类型
适用于搜索Text,NText,Varchar,Nvarchar,Char,NChar等类型
1、创建存储过程:My_Search_StringInGivenTable
代码如下:
USE [NORTHWIND]
GO
/****** Object: StoredProcedure [dbo].[My_Search_StringInGivenTable] Script Date: 09/25/2011 15:37:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[My_Search_StringInGivenTable]
(@SearchString NVARCHAR(MAX),
@Table_Schema sysname,
@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 = 'cast(NULL as nvarchar(max))'
-- set select statement using dynamic UNPIVOT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'select *, ' + QUOTENAME(@Table_Schema,'''') + 'as [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' as [Table Name]' +
' from
(select '+ @PkColumn + ' as [PK Column], ' + @Cols + ' from ' + QUOTENAME(@Table_Name) +
' )src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt
WHERE [Column Value] LIKE ''%'' + @SearchString + ''%'''
--print @SQL
EXECUTE sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString
END

2、创建搜索存储过程:My_Search_String_AllTables
此存储过程将遍历指定数据库的所有表,并利用上面创建的存储过程My_Search_StringInGivenTable来取得每个表的搜索结果。
代码如下:
USE [NORTHWIND]
GO
/****** Object: StoredProcedure [dbo].[My_Search_String_AllTables] Script Date: 09/25/2011 15:41:58 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC [dbo].[My_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 My_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

使用示例
代码如下:
USE [NORTHWIND]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[My_Search_String_AllTables]
@SearchString = N'WantValue'
SELECT 'Return Value' = @return_value
GO

还有另一个版本,就是直接创建一个存储过程来取得所要结果,但个人觉得前面那个方法更具灵活性
代码如下:
USE [NORTHWIND]
GO
/****** Object: StoredProcedure [dbo].[ZL_SearchAllTables] Script Date: 09/25/2011 15:44:10 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC [dbo].[ZL_SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
[code]
二、搜索数据是Int类型
适用于搜索smallint, tinyint, int, bigint等类型
1、创建存储过程 My_Search_IntInGivenTable
[code]
USE [NORTHWIND]
GO
/****** Object: StoredProcedure [dbo].[My_Search_IntInGivenTable] Script Date: 09/25/2011 15:45:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[My_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 = 'cast(NULL as nvarchar(max))'
-- set select statement using dynamic UNPIVOT
SET @SQL = 'select *, ' + QUOTENAME(@Table_Schema, '''')
+ 'as [Table Schema], ' + QUOTENAME(@Table_Name, '''')
+ ' as [Table Name]' + ' from
(select ' + @PkColumn + ' as [PK Column], ' + @Cols + ' from '
+ QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name)
+ ' )src UNPIVOT ([Column Value] for [Column Name] IN ('
+ @Columns + ')) 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

2、创建搜索存储过程My_Search_Int_AllTables,与上面类似,此存储过程将调用 My_Search_IntInGivenTable来实现所遍历的每一个数据表的搜索结果
代码如下:
USE [NORTHWIND]
GO
/****** Object: StoredProcedure [dbo].[My_Search_Int_AllTables] Script Date: 09/25/2011 15:48:29 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC [dbo].[My_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 My_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

使用示例
代码如下:
USE [NORTHWIND]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[My_Search_Int_AllTables]
@SearchValue = 68
SELECT 'Return Value' = @return_value
GO

Note:
1、你可以根据上面一、二中的第1个存储过程来实现只搜索指定某些数据表的功能。
2、对于其它数据类型如:Date,Real等等均可以此为参照进行修改。
3、此方法对大型数据库会很耗时,所以尽量在小数据库上调试。当需要在大数据库上操作时,尽量避开数据库使用高峰时段并要有耐心。
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template