اجرای دستورات SQL در تمامی دیتابیس‌ها: راهکار بهینه و جایگزین sp_MSForEachDB
اغلب اوقات نیاز به اجرای دستورات SQL بر روی تمامی دیتابیس‌های موجود در یک نمونه SQL Server داریم. متداول‌ترین روشی که بسیاری از توسعه‌دهندگان و مدیران دیتابیس برای این کار استفاده می‌کنند، رویه ذخیره‌شده (Stored Procedure) مستند نشده `sp_MSForEachDB` است. این رویه سیستمی به شما امکان می‌دهد تا یک دستور را برای هر دیتابیس اجرا کنید.
برای مثال، می‌توانید از دستور زیر برای اجرای یک کوئری ساده در تمامی دیتابیس‌ها استفاده کنید:
EXEC sp_MSForEachDB 'SELECT DB_NAME() AS DatabaseName, COUNT(*) AS TotalRows FROM ?.dbo.MyTable;'
این دستور کوئری `SELECT DB_NAME() AS DatabaseName, COUNT(*) AS TotalRows FROM ?.dbo.MyTable;` را در هر دیتابیس اجرا می‌کند و `؟` به نام دیتابیس فعلی اشاره دارد.
با این حال، رویه `sp_MSForEachDB` دارای محدودیت‌ها و مشکلاتی است که استفاده از آن را در محیط‌های تولیدی با چالش مواجه می‌کند. این محدودیت‌ها شامل موارد زیر می‌شوند:
* **نادیده گرفتن دیتابیس‌های سیستمی:** این رویه ممکن است دیتابیس‌های سیستمی را نادیده بگیرد، یا برعکس، ممکن است دیتابیس‌های سیستمی را نیز شامل شود که همیشه مطلوب نیست.
* **دیتابیس‌های آفلاین یا محدود:** `sp_MSForEachDB` به طور پیش‌فرض دیتابیس‌هایی که در وضعیت آفلاین (Offline) یا دسترسی محدود (Restricted Access) هستند را شامل نمی‌شود، که می‌تواند منجر به عدم اجرای دستور در تمام دیتابیس‌های مورد نظر شود.
* **مدیریت خطا:** مدیریت خطا در این رویه به خوبی صورت نمی‌گیرد. به عنوان مثال، استفاده از بلوک‌های `TRY/CATCH` همیشه به درستی کار نمی‌کند و ممکن است خطاهای یک دستور را نادیده بگیرد.
* **خروجی و ترکیب نتایج:** ترکیب نتایج خروجی از هر دیتابیس به یک مجموعه نتایج واحد دشوار است. برای مثال، اگر بخواهید اطلاعاتی را از هر دیتابیس جمع‌آوری کرده و در یک جدول موقت ذخیره کنید، `sp_MSForEachDB` این قابلیت را به راحتی ارائه نمی‌دهد.
با توجه به این مشکلات، یک راهکار سفارشی می‌تواند جایگزین مناسبی باشد. در ادامه، یک رویه ذخیره‌شده سفارشی به نام `sp_ForEachDB` را معرفی می‌کنیم که این محدودیت‌ها را برطرف می‌کند و انعطاف‌پذیری بیشتری را برای اجرای دستورات در دیتابیس‌های مختلف فراهم می‌آورد.
این رویه ذخیره‌شده سفارشی پارامترهای مختلفی را برای کنترل دقیق نحوه اجرای دستورات ارائه می‌دهد:
* `@command nvarchar(MAX)`: دستوری که قرار است در هر دیتابیس اجرا شود.
* `@captureOutput bit = 0`: اگر ۱ باشد، خروجی دستورات را در یک جدول موقت به نام `#sp_ForEachDB_Output` ذخیره می‌کند.
* `@userDatabasesOnly bit = 0`: اگر ۱ باشد، دستور فقط در دیتابیس‌های کاربری اجرا می‌شود و دیتابیس‌های سیستمی را نادیده می‌گیرد.
* `@onlineOnly bit = 0`: اگر ۱ باشد، دستور فقط در دیتابیس‌های آنلاین اجرا می‌شود.
* `@suppressErrors bit = 0`: اگر ۱ باشد، خطاهای رخ داده در اجرای دستورات را نادیده می‌گیرد و روند اجرا ادامه می‌یابد.
* `@printCommands bit = 0`: اگر ۱ باشد، دستورات تولید شده برای هر دیتابیس را چاپ می‌کند اما آن‌ها را اجرا نمی‌کند (برای دیباگ).
* `@delimiter nvarchar(10) = N”`: یک جداکننده اختیاری که برای ترکیب نتایج در صورت `captureOutput = 1` استفاده می‌شود.
* `@debug bit = 0`: اگر ۱ باشد، اطلاعات دیباگ بیشتری را چاپ می‌کند.
در ادامه کد کامل رویه `sp_ForEachDB` آمده است:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('sp_ForEachDB') IS NOT NULL
DROP PROCEDURE sp_ForEachDB
GO
CREATE PROCEDURE [dbo].[sp_ForEachDB]
@command nvarchar(MAX),
@captureOutput bit = 0,
@userDatabasesOnly bit = 0,
@onlineOnly bit = 0,
@suppressErrors bit = 0,
@printCommands bit = 0,
@delimiter nvarchar(10) = N'',
@debug bit = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL nvarchar(MAX);
DECLARE @db_name sysname;
DECLARE @db_id int;
DECLARE @db_state tinyint; -- 0=ONLINE, 1=RESTORING, 2=RECOVERING, 3=RECOVERY_PENDING, 4=SUSPECT, 5=EMERGENCY, 6=OFFLINE, 7=COPYING, 10=OFFLINE_BY_SCRIPT, 11=ONLINE_BY_SCRIPT, 12=STANDBY_READONLY, 13=STANDBY_FULLACCESS
DECLARE @is_read_only bit;
DECLARE @is_in_standby bit;
DECLARE @is_shutdown bit;
DECLARE @is_online bit;
DECLARE @is_user_database bit;
IF @captureOutput = 1
BEGIN
IF OBJECT_ID('tempdb..#sp_ForEachDB_Output') IS NOT NULL
DROP TABLE #sp_ForEachDB_Output;
SET @SQL = N'CREATE TABLE #sp_ForEachDB_Output (
DatabaseName sysname,
OutputValue nvarchar(MAX)
);';
EXEC (@SQL);
IF @debug = 1 PRINT 'Created #sp_ForEachDB_Output table.';
END
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT database_id, name, state, is_read_only, is_in_standby, is_shutdown
FROM sys.databases
ORDER BY name;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_id, @db_name, @db_state, @is_read_only, @is_in_standby, @is_shutdown;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @is_online = CASE WHEN @db_state = 0 THEN 1 ELSE 0 END;
SET @is_user_database = CASE WHEN @db_id > 4 THEN 1 ELSE 0 END; -- 1=master, 2=tempdb, 3=model, 4=msdb
IF @debug = 1
BEGIN
PRINT N'Processing database: ' + @db_name + N' (ID: ' + CAST(@db_id AS nvarchar) + N')';
PRINT N' State: ' + CAST(@db_state AS nvarchar) + N' (Online: ' + CAST(@is_online AS nvarchar) + N')';
PRINT N' User DB: ' + CAST(@is_user_database AS nvarchar);
END
IF (@userDatabasesOnly = 1 AND @is_user_database = 0)
BEGIN
IF @debug = 1 PRINT N' Skipping (User Databases Only is 1 and this is a system database).';
GOTO NextDB;
END
IF (@onlineOnly = 1 AND @is_online = 0)
BEGIN
IF @debug = 1 PRINT N' Skipping (Online Only is 1 and this database is not online).';
GOTO NextDB;
END
SET @SQL = N'USE ' + QUOTENAME(@db_name) + N';' + @command;
IF @printCommands = 1
BEGIN
PRINT N'--- Command for ' + @db_name + N' ---';
PRINT @SQL;
END
ELSE
BEGIN
IF @debug = 1 PRINT N' Executing command: ' + @SQL;
IF @captureOutput = 1
BEGIN
BEGIN TRY
-- Use INSERT...EXEC to capture results from @command, if it returns a single string result
SET @SQL = N'USE ' + QUOTENAME(@db_name) + N';
INSERT INTO #sp_ForEachDB_Output (DatabaseName, OutputValue)
SELECT ''' + REPLACE(REPLACE(@db_name, '''', ''''''), N'\', N'\\') + N''' AS DatabaseName,
COALESCE(CAST((' + @command + N') AS nvarchar(MAX)), N''NULL'') AS OutputValue;';
IF @debug = 1 PRINT N' Capture Output Command: ' + @SQL;
EXEC (@SQL);
END TRY
BEGIN CATCH
IF @suppressErrors = 0
BEGIN
INSERT INTO #sp_ForEachDB_Output (DatabaseName, OutputValue)
SELECT QUOTENAME(@db_name) AS DatabaseName,
N'ERROR: ' + ERROR_MESSAGE();
IF @debug = 1 PRINT N' Error capturing output for ' + @db_name + N': ' + ERROR_MESSAGE();
-- If @suppressErrors is 0, we still want to log the error but not re-throw if it's for capturing output.
-- The main command execution (without captureOutput) will handle re-throwing if @suppressErrors is 0.
END
ELSE
BEGIN
IF @debug = 1 PRINT N' Error capturing output for ' + @db_name + N': ' + ERROR_MESSAGE() + N' (Errors suppressed).';
END
END CATCH
END
ELSE
BEGIN
BEGIN TRY
EXEC (@SQL);
END TRY
BEGIN CATCH
IF @suppressErrors = 0
BEGIN
DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
IF @debug = 1 PRINT N' Error executing command for ' + @db_name + N': ' + @ErrorMessage;
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END
ELSE
BEGIN
IF @debug = 1 PRINT N' Error executing command for ' + @db_name + N': ' + ERROR_MESSAGE() + N' (Errors suppressed).';
END
END CATCH
END
END
NextDB:
FETCH NEXT FROM db_cursor INTO @db_id, @db_name, @db_state, @is_read_only, @is_in_standby, @is_shutdown;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
IF @captureOutput = 1
BEGIN
IF @delimiter IS NULL OR @delimiter = N''
BEGIN
SELECT DatabaseName, OutputValue
FROM #sp_ForEachDB_Output
ORDER BY DatabaseName;
END
ELSE
BEGIN
SELECT STUFF(
(SELECT @delimiter + N'DB: ' + DatabaseName + N' Val: ' + OutputValue
FROM #sp_ForEachDB_Output
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
,1,LEN(@delimiter),N'') AS ConcatenatedOutput;
END
IF @debug = 1 PRINT 'Displaying captured output from #sp_ForEachDB_Output table.';
END
END
GO
این رویه `sp_ForEachDB` با استفاده از یک `CURSOR` بر روی دیتابیس‌های `sys.databases` پیمایش می‌کند و دستور ارسالی را در هر دیتابیس اجرا می‌کند. قابلیت‌های کلیدی آن عبارتند از:
* **فیلتر کردن دیتابیس‌ها:** با پارامترهای `@userDatabasesOnly` و `@onlineOnly` می‌توانید انتخاب کنید که دستور فقط در دیتابیس‌های کاربری یا دیتابیس‌های آنلاین اجرا شود.
* **مدیریت خروجی:** پارامتر `@captureOutput` امکان ذخیره خروجی هر دستور را در یک جدول موقت `#sp_ForEachDB_Output` فراهم می‌کند. این قابلیت به خصوص برای جمع‌آوری داده‌ها از دیتابیس‌های مختلف بسیار مفید است.
* **کنترل خطا:** این رویه از بلوک‌های `TRY/CATCH` داخلی استفاده می‌کند. پارامتر `@suppressErrors` به شما اجازه می‌دهد تا تصمیم بگیرید که آیا خطاها باید منجر به توقف اجرا شوند یا نادیده گرفته شوند.
* **حالت دیباگ و چاپ دستور:** پارامترهای `@printCommands` و `@debug` به شما کمک می‌کنند تا دستورات SQL تولید شده را مشاهده کرده و فرآیند اجرا را اشکال‌زدایی (Debug) کنید بدون اینکه واقعاً دستوری اجرا شود.
**نحوه استفاده از `sp_ForEachDB`**
در اینجا چند مثال برای نشان دادن نحوه استفاده از `sp_ForEachDB` آورده شده است:
**مثال ۱: اجرای یک دستور ساده در تمامی دیتابیس‌ها**
برای بررسی فضای استفاده شده توسط فایل‌های لاگ در هر دیتابیس، می‌توانید از دستور زیر استفاده کنید:
EXEC sp_ForEachDB @command = 'DBCC SQLPERF(LOGSPACE);'
این دستور گزارش فضای لاگ را برای هر دیتابیس نمایش می‌دهد.
**مثال ۲: اجرای دستور فقط در دیتابیس‌های کاربری**
اگر می‌خواهید فقط دیتابیس‌های کاربری را هدف قرار دهید، پارامتر `@userDatabasesOnly` را روی `1` تنظیم کنید:
EXEC sp_ForEachDB @command = 'SELECT DB_NAME() AS DatabaseName;', @userDatabasesOnly = 1
این دستور نام هر دیتابیس کاربری را برمی‌گرداند.
**مثال ۳: جمع‌آوری خروجی در یک جدول موقت**
برای جمع‌آوری خروجی یک دستور (مانند شمارش ردیف‌های یک جدول) از هر دیتابیس در یک جدول موقت، از پارامتر `@captureOutput` استفاده کنید:
EXEC sp_ForEachDB @command = 'SELECT COUNT(*) FROM sys.objects;', @captureOutput = 1
پس از اجرای این دستور، می‌توانید نتایج جمع‌آوری شده را از جدول `#sp_ForEachDB_Output` بازیابی کنید:
SELECT DatabaseName, OutputValue FROM #sp_ForEachDB_Output;
**مثال ۴: مدیریت خطا و مشاهده دستورات تولید شده**
برای چاپ دستورات تولید شده بدون اجرای آن‌ها و همچنین دیدن جزئیات دیباگ، می‌توانید از پارامترهای `@printCommands` و `@debug` استفاده کنید:
EXEC sp_ForEachDB @command = 'SELECT 1/0;', @suppressErrors = 1, @printCommands = 1, @debug = 1;
در این مثال، دستوری که منجر به خطا می‌شود (تقسیم بر صفر) اجرا نخواهد شد زیرا `printCommands` روی `1` تنظیم شده است. اگر `printCommands` صفر باشد و `suppressErrors` روی `1` باشد، خطاها نادیده گرفته می‌شوند.
**مثال ۵: استفاده از جداکننده برای خروجی ترکیب شده**
اگر می‌خواهید خروجی را با یک جداکننده مشخص ترکیب کنید، از پارامتر `@delimiter` استفاده کنید:
EXEC sp_ForEachDB @command = 'SELECT DB_NAME() + N''-Test'';', @captureOutput = 1, @delimiter = N' | '
این دستور خروجی را به صورت یک رشته واحد و با جداکننده ` | ` برمی‌گرداند.
با استفاده از `sp_ForEachDB` می‌توانید به راحتی و با انعطاف‌پذیری بالا، دستورات SQL خود را در تمامی دیتابیس‌های SQL Server اجرا کنید و مشکلات مربوط به `sp_MSForEachDB` را دور بزنید. این رویه، ابزاری قدرتمند برای اتوماسیون وظایف مدیریتی و توسعه‌ای در محیط‌های پیچیده SQL Server است.
