有没有办法在SQL Server数据库的所有表中搜索字符串?
我想搜索字符串，比如john。结果应该显示包含john的表和它们各自的行。
当前回答
有点晚了，但是您可以很容易地用这个查询找到一个字符串
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'StringtoSearch'
DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id
AND system_type_id IN (167, 175, 231, 239)
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + ']
LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
EXECUTE(@sql_string)
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur
其他回答
这很有帮助。我想把这个函数导入到Postgre SQL数据库。我想把它分享给感兴趣的人。会有几个小时。注意:这个函数创建了一个SQL语句列表，可以在Postgre数据库上复制和执行。也许比我聪明的人可以让Postgre在一个函数中创建并执行所有语句。
CREATE OR REPLACE FUNCTION SearchAllTables(_search text) RETURNS TABLE( txt text ) as $funct$
DECLARE __COUNT int;
__SQL text;
BEGIN
EXECUTE 'SELECT COUNT(0) FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = ''text''
AND table_schema = ''public'' ' INTO __COUNT;
RETURN QUERY
SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY table_name) < __COUNT THEN
'SELECT ''' || table_name ||'.'|| column_name || ''' AS tbl, "' || column_name || '" AS col FROM "public"."' || "table_name" || '" WHERE "'|| "column_name" || '" ILIKE ''%' || _search || '%'' UNION ALL'
ELSE
'SELECT ''' || table_name ||'.'|| column_name || ''' AS tbl, "' || column_name || '" AS col FROM "public"."' || "table_name" || '" WHERE "'|| "column_name" || '" ILIKE ''%' || _search || '%'''
END AS txt
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'text'
AND table_schema = 'public';
END
$funct$ LANGUAGE plpgsql;
有点晚了，但是您可以很容易地用这个查询找到一个字符串
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'StringtoSearch'
DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id
AND system_type_id IN (167, 175, 231, 239)
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + ']
LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
EXECUTE(@sql_string)
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur
不要忽略@Brandon的精彩回答，但OP要求“结果应该显示表及其各自的行”。我基于来自@Brandon的原始答案创建了一个记录级解决方案，该解决方案还通过简单地将所有适当的列连接到一个字符串中来避免使用其他人在这里使用的游标。(我感觉这样做会更好，但我真的没有比较过结果-请随意这样做并在这里提供反馈。)
注意，我使用了STRING_AGG函数来连接列。如果你使用的是旧版本的SQL Server，你可能需要使用可以在网上找到的替代方法之一。
CREATE PROC [dbo].[SearchAllTablesByRecord]
(
@SearchStr NVARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TableName NVARCHAR(256), @SearchStr2 NVARCHAR(110)
DECLARE @ColumnList NVARCHAR(2048), @ColumnJoin NVARCHAR(2048)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
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
)
PRINT @TableName -- For progress monitoring
SET @ColumnList =
(
SELECT STRING_AGG(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')
)
SET @ColumnJoin = REPLACE(@ColumnList,',','+')
IF @ColumnList <> ''
EXEC
(
'SELECT ''' + @TableName + ''' AS [Table],' + @ColumnList + '
INTO #Results
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnJoin + ' LIKE ' + @SearchStr2 + '
IF @@ROWCOUNT > 0 SELECT * FROM #Results'
)
END
END
我已经为这个写了一个SP，它以表名的形式返回搜索结果，在其中找到搜索关键字字符串的列名以及搜索对应的行，如下面的屏幕截图所示。
这可能不是最有效的解决方案，但您可以根据需要随时修改和使用它。
IF OBJECT_ID('sp_KeywordSearch', 'P') IS NOT NULL
DROP PROC sp_KeywordSearch
GO
CREATE PROCEDURE sp_KeywordSearch @KeyWord NVARCHAR(100)
AS
BEGIN
DECLARE @Result TABLE
(TableName NVARCHAR(300),
ColumnName NVARCHAR(MAX))
DECLARE @Sql NVARCHAR(MAX),
@TableName NVARCHAR(300),
@ColumnName NVARCHAR(300),
@Count INT
DECLARE @tableCursor CURSOR
SET @tableCursor = CURSOR LOCAL SCROLL FOR
SELECT N'SELECT @Count = COUNT(1) FROM [dbo].[' + T.TABLE_NAME + '] WITH (NOLOCK) WHERE CAST([' + C.COLUMN_NAME +
'] AS NVARCHAR(MAX)) LIKE ''%' + @KeyWord + N'%''',
T.TABLE_NAME,
C.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES AS T WITH (NOLOCK)
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C WITH (NOLOCK)
ON T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
T.TABLE_NAME = C.TABLE_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE' AND
C.TABLE_SCHEMA = 'dbo' AND
C.DATA_TYPE NOT IN ('image', 'timestamp')
OPEN @tableCursor
FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Count = 0
EXEC sys.sp_executesql
@Sql,
N'@Count INT OUTPUT',
@Count OUTPUT
IF @Count > 0
BEGIN
INSERT INTO @Result
(TableName, ColumnName)
VALUES (@TableName, @ColumnName)
END
FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
END
CLOSE @tableCursor
DEALLOCATE @tableCursor
SET @tableCursor = CURSOR LOCAL SCROLL FOR
SELECT SUBSTRING(TB.Sql, 1, LEN(TB.Sql) - 3) AS Sql, TB.TableName, SUBSTRING(TB.Columns, 1, LEN(TB.Columns) - 1) AS Columns
FROM (SELECT R.TableName, (SELECT R2.ColumnName + ', ' FROM @Result AS R2 WHERE R.TableName = R2.TableName FOR XML PATH('')) AS Columns,
'SELECT * FROM ' + R.TableName + ' WITH (NOLOCK) WHERE ' +
(SELECT 'CAST(' + R2.ColumnName + ' AS NVARCHAR(MAX)) LIKE ''%' + @KeyWord + '%'' OR '
FROM @Result AS R2
WHERE R.TableName = R2.TableName
FOR
XML PATH('')) AS Sql
FROM @Result AS R
GROUP BY R.TableName) TB
ORDER BY TB.Sql
OPEN @tableCursor
FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @Sql
SELECT @TableName AS [Table],
@ColumnName AS Columns
EXEC(@Sql)
FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
END
CLOSE @tableCursor
DEALLOCATE @tableCursor
END
如果您像我一样，在生产环境中有某些限制，那么您可能希望使用表变量而不是临时表，使用临时查询而不是创建过程。
当然，根据您的sql server实例，它必须支持表变量。
我还添加了一个USE语句来缩小搜索范围
USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
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', 'int', 'decimal')
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
推荐文章
- 在SQL Server中查找重复的行
- 执行带有Output参数的存储过程?
- 从DateTime中提取小时(SQL Server 2005)
- 我如何在T-SQL用逗号格式化一个数字?
- LEFT OUTER JOIN如何返回比左表中存在的记录更多的记录?
- 如何用SQL语句计算百分比
- Postgres唯一约束与索引
- SQL Server动态PIVOT查询?
- 如何等待2秒?
- SQL Server: CROSS JOIN和FULL OUTER JOIN的区别是什么?
- MySQL对重复键更新在一个查询中插入多行
- varchar和nvarchar SQL Server数据类型之间的主要性能差异是什么?
- 向现有表添加主键
- 使用电子邮件地址为主键?
- MySQL:如何复制行，但改变几个字段?
