在大型数据库中搜索特定ID？ [英] Searching for a specific ID in a large database?

问题描述

我需要在非常大的数据库中查找ID。 ID为：

I need to look up an ID in a very large database. The ID is:

0167a901-e343-4745-963c-404809b74dd9

数据库有大量表格，数百万行在大表格中。

The database has hundreds of tables, and millions of rows in the big tables.

日期在过去2或3个月内，但这是关于它。我正在寻找任何线索，如何缩小这样的搜索。

I can narrow the date to within the last 2 or 3 months, but that's about it. I'm looking for any clues as to how to narrow down searches like this.

我很好奇的一件事是使用 LIKE 搜索是否有帮助。

One thing I'm curious about is whether using LIKE searches helps.

即它有助于像

select top 10 * 
from BIG_TABLE
where DESIRED_ID like '016%'

任何提示/建议都非常感谢。正在远程访问数据库，这是挑战的一部分

Any tips/suggestions are greatly appreciated . The database is being accessed remotely so that's part of the challenge

推荐答案

我有几个年前为一个类似的目的，虽然有文本字段。它查找合格的列，然后在这些列中搜索该值。因为你有一个非确定性的范围，你可能不能做得比这样的好。

I have this script that I built several years ago for a similar purpose, albeit with text fields. It finds eligible columns, and then searches through those columns for the value. As you have a non-deterministic scope, you may not be able to do better than something like this.

你可能想要调整它包括uniqueidentifier列 - 如果这实际上是数据类型 - 或使用等号而不是类似的搜索。

You may want to tweak it a bit to include uniqueidentifier columns - if that is actually the datatype - or use an equal instead of a like search.

如果这是你要定期重用，你可以喂它一个列表

If this is something you are going to reuse periodically, you could feed it a list of common tables or columns to find these things in, so it doesnt take as long to find things.

/*This script will find any text value in the database*/
/*Output will be directed to the Messages window. Don't forget to look there!!!*/

SET NOCOUNT ON
DECLARE @valuetosearchfor varchar(128), @objectOwner varchar(64)
SET @valuetosearchfor = '%putYourGuidHere%' --should be formatted as a like search 
SET @objectOwner = 'dbo'

DECLARE @potentialcolumns TABLE (id int IDENTITY, sql varchar(4000))

INSERT INTO @potentialcolumns (sql)
SELECT 
    ('if exists (select 1 from [' +
    [tabs].[table_schema] + '].[' +
    [tabs].[table_name] + 
    '] (NOLOCK) where [' + 
    [cols].[column_name] + 
    '] like ''' + @valuetosearchfor + ''' ) print ''SELECT * FROM [' +
    [tabs].[table_schema] + '].[' +
    [tabs].[table_name] + 
    '] (NOLOCK) WHERE [' + 
    [cols].[column_name] + 
    '] LIKE ''''' + @valuetosearchfor + '''''' +
    '''') as 'sql'
FROM information_schema.columns cols
    INNER JOIN information_schema.tables tabs
        ON cols.TABLE_CATALOG = tabs.TABLE_CATALOG
            AND cols.TABLE_SCHEMA = tabs.TABLE_SCHEMA
            AND cols.TABLE_NAME = tabs.TABLE_NAME
WHERE cols.data_type IN ('char', 'varchar', 'nvchar', 'nvarchar','text','ntext')
    AND tabs.table_schema = @objectOwner
    AND tabs.TABLE_TYPE = 'BASE TABLE'
    AND (cols.CHARACTER_MAXIMUM_LENGTH >= (LEN(@valueToSearchFor) - 2) OR cols.CHARACTER_MAXIMUM_LENGTH = -1)
ORDER BY tabs.table_catalog, tabs.table_name, cols.ordinal_position

DECLARE @count int
SET @count = (SELECT MAX(id) FROM @potentialcolumns)
PRINT 'Found ' + CAST(@count as varchar) + ' potential columns.'
PRINT 'Beginning scan...'
PRINT ''
PRINT 'These columns contain the values being searched for...'
PRINT ''
DECLARE @iterator int, @sql varchar(4000)
SET @iterator = 1
WHILE @iterator <= (SELECT Max(id) FROM @potentialcolumns)
BEGIN
    SET @sql = (SELECT [sql] FROM @potentialcolumns where [id] = @iterator)
    IF (@sql IS NOT NULL) and (RTRIM(LTRIM(@sql)) <> '')
    BEGIN
        --SELECT @sql --use when checking sql output
        EXEC (@sql)
    END
    SET @iterator = @iterator + 1
END

PRINT ''
PRINT 'Scan completed'

$ b b

如果这看起来很糟糕，脚本正在执行这样的语句

If that looks wonky, the script is executing a statement like this

if exists (select 1 from [schema].[table_name] (NOLOCK) 
                    where [column_name] LIKE '%yourValue%')
begin
   print select * from [schema].[table_name] (NOLOCK) 
                    where [column_name] LIKE '%yourValue%'
end

... [schema] ， [table_name] ， [column_name] 和％yourValue％。

对特定架构中的表格进行筛选...

Its filtering on...

< ）

• 只有表格可以调整 c>（n）varchar code> / （n）text 数据类型（添加或更改，可识别数据类型转换）

• tables in a specific schema (filter can be removed)
• only tables, not views (can be adjusted)
• only columns that will hold the search value
• the (n)char/(n)varchar/(n)text data types (add or change, be cognizant of data type conversion)

最后，输出不会转到结果网格。检查邮件窗口（您在哪里看到N行受影响）

Lastly, output does not go to the results grid. Check the Messages window (where you see "N rows affected")

这篇关于在大型数据库中搜索特定ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！