我迷失在一个大数据库中，我无法找到我得到的数据。 我想知道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中执行，但需要注意几点：
这是非常慢的。 我在一个小数据库上测试过，只有少数几个表，完成了几分钟。 如果您的数据库太大，您无法理解，那么这可能无法使用。
我把它从袖口上写下来 我没有处理任何错误，可能还有一些其他的问题，特别是因为我不经常使用游标。 例如，我认为有一种方法来刷新列光标，而不是每次关闭/取消分配/重新创建它。
如果你不明白数据库或者不知道哪里的东西来了，那么你应该可以找到一个人。 即使您可以找到数据的位置，也可能在某处复制，或者您可能不了解数据库的其他方面。 如果您的公司中没有人了解数据库，那么您处于一个很大的混乱。
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_curThis code should do it in SQL 2005, but a few caveats:
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更多推荐
发布评论