چند وقتی بود که به دنبال راهی برای جستجو کردن جداول یک 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
امیدوارم که این مطلب به درد دوستان عزیز بخورد.
پیوند ها