如何在单个查询中搜索与数据库中的所有列匹配的值。
我是否需要像我一样查询，或者有什么简单的方法可以实现这一点？
我的代码：-
SET @SearchString='search_string'
SELECT @SQL = @SQL + 'SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + '' matches in column ''+'''
+ C.name + '''+'' on table '' + ''' + SC.name + '.' + T.name +
''' [Matches for '''+@SearchString+''':] FROM ' +
QUOTENAME(SC.name) + '.' + QUOTENAME(T.name) + ' WHERE ' + QUOTENAME(C.name) +
' LIKE ''%' + @SearchString +
'%'' HAVING COUNT(*)>0 UNION ALL ' +CHAR(13) + CHAR(10)
FROM sys.columns C
JOIN sys.tables T
ON C.object_id=T.object_id
JOIN sys.schemas SC
ON SC.schema_id=T.schema_id
JOIN sys.types ST
ON C.user_type_id=ST.user_type_id
JOIN sys.types SYST
ON ST.system_type_id=SYST.user_type_id
AND ST.system_type_id=SYST.system_type_id
WHERE SYST.name IN ('varchar','nvarchar','text','ntext','char','nchar')
ORDER BY T.name, C.name
IF LEN(@SQL)>12
SELECT @SQL=LEFT(@SQL,LEN(@SQL)- 12)
EXEC(@SQL)
你可以试试这段代码，
exec SearchAllTables @SearchStr='Your search string';
这将对相关数据库中的所有表执行不区分大小写的搜索，并整齐地吐出字符串所在的字段。
下面是存储过程：
CREATE PROCEDURE SearchAllTables (@SearchStr NVARCHAR(100))
AS
BEGIN
DECLARE @Results TABLE (
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