جستجو یک مقدار در همه جدول ها و دیتابیس ها در SQL Server

پاسخ هوش مصنوعی

برای جستجوی یک مقدار در تمام جداول و دیتابیس ها در SQL Server، می توانید از یک Stored Procedure به همراه Dynamic SQL استفاده کنید. این روش باعث می شود تا مقدار مورد نظر شما در تمامی جداول و ستون ها جستجو شود. در اینجا یک مثال از یک Stored Procedure آورده ایم:

CREATE PROCEDURE SearchValueInAllDatabases
    @SearchValue NVARCHAR(255)
AS
BEGIN
    DECLARE @DatabaseName NVARCHAR(255)
    DECLARE @TableName NVARCHAR(255)
    DECLARE @ColumnName NVARCHAR(255)
    DECLARE @Sql NVARCHAR(MAX)
    DECLARE @Results TABLE (DatabaseName NVARCHAR(255), TableName NVARCHAR(255), ColumnName NVARCHAR(255))

    DECLARE db_cursor CURSOR FOR
    SELECT name 
    FROM sys.databases 
    WHERE state_desc = 'ONLINE' AND database_id > 4  -- Exclude system databases

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE tbl_cursor CURSOR FOR
        SELECT TABLE_NAME, COLUMN_NAME 
        FROM @DatabaseName.INFORMATION_SCHEMA.COLUMNS

        OPEN tbl_cursor
        FETCH NEXT FROM tbl_cursor INTO @TableName, @ColumnName

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @Sql = 'USE ' + QUOTENAME(@DatabaseName) + ';
                        IF EXISTS (SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + ' = @SearchValue)
                        BEGIN
                            INSERT INTO @Results (DatabaseName, TableName, ColumnName)
                            VALUES (''' + @DatabaseName + ''', ''' + @TableName + ''', ''' + @ColumnName + ''')
                        END'

            EXEC sp_executesql @Sql, N'@SearchValue NVARCHAR(255)', @SearchValue

            FETCH NEXT FROM tbl_cursor INTO @TableName, @ColumnName
        END

        CLOSE tbl_cursor
        DEALLOCATE tbl_cursor

        FETCH NEXT FROM db_cursor INTO @DatabaseName
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

    SELECT * FROM @Results
END

شما می توانید این Stored Procedure را فراخوانی کرده و مقدار مورد نظر خود را به آن بدهید:

EXEC SearchValueInAllDatabases @SearchValue = '15000'

این Stored Procedure تمام دیتابیس ها (به جز دیتابیس های سیستم) را گشته و مقدار مورد نظر را در تمامی جداول و ستون ها جستجو می کند و نتایج را به صورت جدول نمایش می دهد.

جستجو در همه آبجکت های Sql Server

ببینید نرم افزار رایگانی هست که با دانلود و نصب اون میتونید در تمام Sql Server هر آبجکتی رو که بخواید جست و جو کنید. یعنی هر جدول، ستون، Sp، تریگر، ایندکس و ...

ولی دقت کنید این نرم افزار برای جست و جو در آبجکت های دیتابیس هست نه برای جست و جو در اطلاعات و داده های دیتابیس

دانلود رایگان Sql Search

جست و جوی یک مقدار در همه جداول در یک دیتابیس

خب یک کوئری براتون قرار میدم که در واقع یک SP هست. با ایجاد این SP میتونید هر مقداری رو در اطلاعات یک دیتابیس جست و جو کنید. دقت کنید گفتم اطلاعات یک دیتابیس نه همه دیتابیس ها. دلیل اینکه میگم یک دیتابیس هم مشخصه. چون شما وقتی کوئری رو اجرا می کنید باید در داخل یک دیتابیس مشخص اجرا بشه. شما میتونید این SP رو توی همه دیتابیس ها ایجاد کنید و تک تک سرچ کنید.

CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = 'Base Table'
            And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')


DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                            THEN 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       = ''' + @DataToFind + '''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            ELSE 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       Like ''%' + @DataToFind + '%''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            END,
        @PARAMETERS = '@DataExists Bit OUTPUT',
        @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i


        PRINT @SQL
        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

        SET @i = @i + 1
    END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

حالا مثلا می خواید کلمه Ali رو در کل اطلاعات دیتابیس جست و جو کنید :

exec FindMyData_string 'Ali', 0

دقت کنید این که یک کوئری بره توی همه دیتابیس ها بگرده به صورت طبیعی با ساختار دیتابیس نمیخونه. چون یک کوئری درون یک دیتابیس معنی پیدا میکنه. ما نمیتونیم یک کوئری داشته باشیم که درون هیچ دیتابیسی نباشه و به همه دیتابیس ها هم دسترسی داشته باشه. (البته میتونید با کمی تغییر در کوئری بالا کاری که میخواید انجام بدید رو ساده تر کنید.)

منبع 

How do I search an SQL Server database for a string