在数据库的所有表、行和列中搜索字符串 [英] Search for a string in all tables, rows and columns of a DB

问题描述

我迷失在一个大数据库中，无法找到我获得的数据的来源.我想知道是否可以使用 SQL Server 2005 在数据库的所有表、行和列中搜索字符串?

I am lost in a big database and I am not able to find where the data I get comes from. I was wondering if it is possible with SQL Server 2005 to search for a string in all tables, rows and columns of a database?

有没有人知道它是否可能以及如何实现?

Does anybody has an idea if it is possible and how?

推荐答案

这段代码应该在 SQL 2005 中完成，但有一些警告:

This code should do it in SQL 2005, but a few caveats:

1. 速度太慢了.我在一个只有少数表的小型数据库上对其进行了测试，并且花了好几分钟才完成.如果您的数据库太大以至于您无法理解它，那么无论如何它都可能无法使用.

1. It is RIDICULOUSLY slow. I tested it on a small database that I have with only a handful of tables and it took many minutes to complete. If your database is so big that you can't understand it then this will probably be unusable anyway.

这是我从袖口上写下来的.我没有进行任何错误处理，可能还有其他一些草率，特别是因为我不经常使用游标.例如，我认为有一种方法可以刷新列游标，而不是每次都关闭/解除分配/重新创建它.

I wrote this off the cuff. I didn't put in any error handling and there might be some other sloppiness especially since I don't use cursors often. For example, I think there's a way to refresh the columns cursor instead of closing/deallocating/recreating it every time.

如果您无法理解数据库或不知道东西来自哪里，那么您可能应该找一个能理解的人.即使您可以找到数据所在的位置，它也可能在某处重复，或者您不了解数据库的其他方面.如果您公司中没有人了解数据库，那么您将陷入一片混乱.

If you can't understand the database or don't know where stuff is coming from, then you should probably find someone who does. Even if you can find where the data is, it might be duplicated somewhere or there might be other aspects of the database that you don't understand. If no one in your company understands the database then you're in a pretty big mess.

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_schema   SYSNAME,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'Test'

DECLARE tables_cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_schema, @table_name

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL  -- Only strings have this and they always have it

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@column_name) + ' LIKE ''%' + @search_string + '%'') PRINT ''' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ', ' + QUOTENAME(@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_schema, @table_name
END

CLOSE tables_cur

DEALLOCATE tables_cur

这篇关于在数据库的所有表、行和列中搜索字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！