☰

如何在整个 MS SQL 2005 DB 中搜索某个值？

tag-icon • tag-icon sql-server-2005 search
如何在整个 MS SQL 2005 DB 中搜索某个值？

如何在整个 MS SQL 2005 DB 中搜索某个值？例如“ABC12345”。

谢谢。

答案1

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

看https://stackoverflow.com/questions/593746/sql-to-search-the-entire-ms-sql-2000-database-for-a-value了解详情。

JR

答案2

我发现这个脚本很有帮助...

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

答案3

也在这里讨论：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给出了一个简单的解决方案，即使用http://www.ssmstoolspack.com/（看http://www.ssmstoolspack.com/images/SDD.png）。

相关内容