如何在整个数据库中查找字符串? [英] How to find a string inside a entire database?

问题描述

我有一个特定的字符串，例如123abcd"，但我不知道表的名称，甚至不知道 SQL Server 数据库中表内列的名称.我想通过选择找到它并显示相关字符串的所有列，所以我想知道这样的事情:

I have one specific string, such as "123abcd" for example but I don't know the name of the table or even the name of the column inside the table on my SQL Server Database. I want to find it with a select and show all columns of the related string, so I was wondering something like:

select * from Database.dbo.* where * like  '%123abcd%'

由于显而易见的原因它不起作用，但是有一种简单的方法可以创建一个 select 语句来做这样的事情吗?

For obvious reasons it doens't work, but there is a simple way to create a select statement to do something like this?

推荐答案

这会起作用:

DECLARE @MyValue NVarChar(4000) = 'something';

SELECT S.name SchemaName, T.name TableName
INTO #T
FROM sys.schemas S INNER JOIN
     sys.tables T ON S.schema_id = T.schema_id;

WHILE (EXISTS (SELECT * FROM #T)) BEGIN
  DECLARE @SQL NVarChar(4000) = 'SELECT * FROM $$TableName WHERE (0 = 1) ';
  DECLARE @TableName NVarChar(1000) = (
    SELECT TOP 1 SchemaName + '.' + TableName FROM #T
  );
  SELECT @SQL = REPLACE(@SQL, '$$TableName', @TableName);

  DECLARE @Cols NVarChar(4000) = '';

  SELECT
    @Cols = COALESCE(@Cols + 'OR CONVERT(NVarChar(4000), ', '') + C.name + ') = CONVERT(NVarChar(4000), ''$$MyValue'') '
  FROM sys.columns C
  WHERE C.object_id = OBJECT_ID(@TableName);

  SELECT @Cols = REPLACE(@Cols, '$$MyValue', @MyValue);
  SELECT @SQL = @SQL + @Cols;

  EXECUTE(@SQL);

  DELETE FROM #T
  WHERE SchemaName + '.' + TableName = @TableName;
END;

DROP TABLE #T;

不过，有几点需要注意.首先，这非常缓慢且未经优化.所有值都被转换为 nvarchar ，以便它们可以无误地进行比较.您可能会遇到诸如 datetime 之类的值没有按预期转换的问题，因此在应该匹配的时候没有匹配(误报).

A couple caveats, though. First, this is outrageously slow and non-optimized. All values are being converted to nvarchar simply so that they can be compared without error. You may run into problems with values like datetime not converting as expected and therefore not being matched when they should be (false negatives).

WHERE (0 = 1) 是为了使构建 OR 子句更容易.如果没有匹配项，您将不会返回任何行.

The WHERE (0 = 1) is there to make building the OR clause easier. If there are not matches you won't get any rows back.

这篇关于如何在整个数据库中查找字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！