如何search整个MS SQL 2005数据库的值？

您查询所有表的INFORMATION_SCHEMA.TABLES，然后查询每个表。

有关详细信息，请参阅https://stackoverflow.com/questions/593746/sql-to-search-the-entire-ms-sql-2000-database-for-a-value 。

JR

我发现这个脚本是有帮助的…

BEGIN TRAN declare @search nvarchar(100) set @search = 'string to search for' -- search whole database for text SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED IF nullif(object_id('tempdb..#tmpSearch'), 0) IS NOT NULL DROP TABLE #tmpSearch CREATE TABLE #tmpSearch ( ListIndex int identity(1,1), CustomSQL nvarchar(2000) ) Print 'Getting tables...' INSERT #tmpSearch (CustomSQL) select 'IF EXISTS (select * FROM [' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @search + '%'') BEGIN PRINT ''Table ' + TABLE_NAME + ', Column ' + COLUMN_NAME + ''';select * FROM [' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @search + '%'' END' FROM information_schema.columns where DATA_TYPE IN ('ntext', 'nvarchar', 'uniqueidentifier', 'char', 'varchar', 'text') and TABLE_NAME NOT IN ('table_you_dont_want_to_look_in', 'and_another_one') Print 'Searching... ' declare @index int declare @customsql nvarchar(2000) WHILE EXISTS (SELECT * FROM #tmpSearch) BEGIN SELECT @index = min(ListIndex) FROM #tmpSearch SELECT @customSQL = CustomSQL FROM #tmpSearch WHERE ListIndex = @index IF @customSql IS NOT NULL EXECUTE (@customSql) SET NOCOUNT ON DELETE #tmpSearch WHERE ListIndex = @index SET NOCOUNT OFF END print 'the end.' ROLLBACK 

这里也讨论： https ： //stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-db

https://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-db/1077129#1077129给出了一个简单的解决scheme是使用http://www.ssmstoolspack.com/ （见http://www.ssmstoolspack.com/images/SDD.png ）。