如何在整个数据库中找到一个字符串？ [英] 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?

推荐答案

p>

This will work:

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）是为了使构建或如果没有匹配，您将不会返回任何行。

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屋！