SQL SERVER 2008

تجارب من در رابطه با SQL SERVER

جستجوی تمامی جداول یک پایگاه داده برای یافتن یک مقدار مورد نظر
ساعت ٥:۱٤ ‎ب.ظ روز ۱۳۸۸/٢/٢٧  کلمات کلیدی: sql server ، stored procedure ، full text search ، جستجو

چند وقتی بود که به دنبال راهی برای جستجو کردن جداول یک database برای یافتن یک مقدار خاص میگشتم. یک راه به نظر خودم میرسید و آن این بود که واقعا می آمدم و یک foreach روی تمام جداول database میزدم و به دنبال مقدار مورد نظر خودم میشگتم. اما به نظرم آمد که شاید راه حل بهتری هم موجود باشد، به همین دلیل بود که روی اینترنت یک جستجوی مختصر کردم و به نتایج جالب زیر رسیدم :
شما میتوانید یک stored procedure بسازید که محتوای آن در ادامه بیان میشود. این ساختار یک رشته را به عنوان ورودی دریافت میکند و آن را به عنوان کلمه مورد جستجو در نظر میگیرد. سپس روی تمام ستونهای از نوع char، varchar، nchar و nvarchar موجود در جداول database جستجو میکند (البته روی جداول سیستمی این کار ار انجام نمیدهد) و دنبال نتایج مورد نظر میگردد. در ضمن میتوان این stored procedure را برای پشتیبانی از دیگر انواع داده ای گسترش داد.
خروجی این sp دارای دو ستون است :
-    نام جدول و نام ستونی که مقدار مورد نظر در آن پیدا شده
-    محتوای واقعی ستون (البته تا ۳۶۳۰ کاراکتر نمایش داده میشود).

برای استفاده از این sp باید کمی محتاط باشید، زیرا این عملیات روی ساختارهای کوچک بسیار سریع است اما برای ساختارهای بزرگ با تعداد جدول زیاد و حجم داده بالا روی آنها بسیار زمانگیر است. پس اگر میخواهید از آن استفاده کنید بهتر است که قبلا کامل فکرهایخود را انجام دهید. این بهتر که در چنین مواقعی از Full-Text Search استفاده نمایید که این کار هم مشکلات خود را دارد.
اسکریپت زیر روی پایگاه داده Pubs به دنبال کلمه Computer میگردد :


EXEC SearchAllTables 'Computer'
GO


و این هم کد کامل store procedure میباشد :


CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN


    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
   
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

 

امیدوارم که این مطلب به درد دوستان عزیز بخورد.

مرجع