اسکریپت لیست دیتابیس‌های دارای یک جدول خاص در SQL Server

اسکریپت لیست دیتابیس‌های دارای یک جدول خاص در SQL Server

در محیط SQL Server ممکن است یک سرور شامل تعداد زیادی دیتابیس باشد و بخواهیم بررسی کنیم یک جدول مشخص در کدام‌یک از این دیتابیس‌ها وجود دارد.
انجام این کار به‌صورت دستی، مخصوصاً زمانی که تعداد دیتابیس‌ها زیاد باشد، زمان‌بر است.
برای مثال ممکن است بخواهیم بدانیم جدول Users در کدام دیتابیس‌ها قرار دارد یا یک جدول خاص در کدام دیتابیس‌ها ایجاد شده است.
در این شرایط می‌توان با استفاده از اسکریپت‌های T-SQL، دیتابیس‌های موجود روی SQL Server را بررسی کرد و فهرستی از دیتابیس‌هایی که جدول موردنظر را دارند به دست آورد.

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

چرا پیدا کردن یک جدول در چند دیتابیس مهم است؟

در پروژه‌های مختلف ممکن است اطلاعات بین چند دیتابیس تقسیم شده باشد.
همچنین در بعضی سازمان‌ها برای هر مشتری، شرکت یا شعبه یک دیتابیس جداگانه ایجاد می‌شود.
در چنین شرایطی ممکن است ساختار دیتابیس‌ها مشابه باشد و یک جدول مشخص در بسیاری از آن‌ها وجود داشته باشد.
برای مثال فرض کنید جدولی با نام Customers دارید و می‌خواهید بدانید این جدول در کدام دیتابیس‌ها وجود دارد.
بررسی تک‌تک دیتابیس‌ها با دستورهایی مانند USE DatabaseName روش مناسبی نیست؛ زیرا باید نام دیتابیس‌ها را یکی‌یکی وارد کرده و ساختار هرکدام را بررسی کنید.
بهتر است این فرآیند را با یک اسکریپت خودکار انجام دهیم.

استفاده از INFORMATION_SCHEMA

یکی از روش‌های ساده برای بررسی وجود جدول، استفاده از INFORMATION_SCHEMA.TABLES است.
برای بررسی جدول در یک دیتابیس می‌توان از دستور زیر استفاده کرد:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Users';

اگر جدول Users در دیتابیس وجود داشته باشد، نام آن نمایش داده می‌شود.
اما مشکل اینجاست که INFORMATION_SCHEMA.TABLES مربوط به دیتابیس فعلی است.
بنابراین اگر بخواهیم چندین دیتابیس را بررسی کنیم، باید دستورات را برای هر دیتابیس اجرا کنیم.

استفاده از sys.tables

روش دیگری که در SQL Server بسیار کاربردی است، استفاده از کاتالوگ سیستمی sys.tables است.
مثلاً:

SELECT name
FROM sys.tables
WHERE name = 'Users';

در صورتی که جدول موردنظر در دیتابیس فعلی وجود داشته باشد، نام آن برگردانده می‌شود.
برای جستجو در تمام دیتابیس‌ها باید بتوانیم این دستور را به‌صورت خودکار برای دیتابیس‌های مختلف اجرا کنیم.

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

یکی از روش‌های متداول برای این کار، استفاده از Dynamic SQL است.
نمونه زیر دیتابیس‌های آنلاین را بررسی می‌کند و در صورت وجود جدول موردنظر، نام دیتابیس را نمایش می‌دهد:

DECLARE @TableName NVARCHAR(128) = N'Users';
DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL = @SQL + '
IF EXISTS (
    SELECT 1
    FROM ' + QUOTENAME(name) + '.sys.tables
    WHERE name = N''' + REPLACE(@TableName, '''', '''''') + '''
)
BEGIN
    PRINT N''' + REPLACE(name, '''', '''''') + ''';
END;
'
FROM sys.databases
WHERE state_desc = 'ONLINE'
  AND database_id > 4;
EXEC sp_executesql @SQL;

در این اسکریپت متغیر @TableName نام جدولی را مشخص می‌کند که قصد جستجوی آن را داریم.
در مثال بالا نام جدول Users است. می‌توانید آن را با نام جدول موردنظر خود تغییر دهید.

خروجی بهتر با SELECT در SQL Server

استفاده از PRINT برای بررسی سریع مناسب است، اما اگر بخواهیم نتیجه را به‌صورت یک لیست قابل استفاده داشته باشیم، بهتر است اطلاعات را داخل یک جدول موقت ذخیره کنیم.
برای مثال:

DECLARE @TableName NVARCHAR(128) = N'Users';
DECLARE @SQL NVARCHAR(MAX) = N'';
CREATE TABLE #Databases
(
    DatabaseName SYSNAME
);
SELECT @SQL = @SQL + '
IF EXISTS
(
    SELECT 1
    FROM ' + QUOTENAME(name) + '.sys.tables
    WHERE name = N''' + REPLACE(@TableName, '''', '''''') + '''
)
BEGIN
    INSERT INTO #Databases(DatabaseName)
    VALUES (N''' + REPLACE(name, '''', '''''') + ''');
END;
'
FROM sys.databases
WHERE state_desc = 'ONLINE'
  AND database_id > 4;
EXEC sp_executesql @SQL;
SELECT DatabaseName
FROM #Databases
ORDER BY DatabaseName;
DROP TABLE #Databases;

در این حالت، خروجی به شکل یک لیست از نام دیتابیس‌ها نمایش داده می‌شود.

چرا از QUOTENAME استفاده می‌کنیم؟

هنگام ساخت Dynamic SQL باید به نام دیتابیس‌ها توجه داشته باشیم.
ممکن است نام یک دیتابیس دارای فاصله یا بعضی کاراکترهای خاص باشد.
استفاده از:

QUOTENAME(name)

باعث می‌شود نام دیتابیس به شکل مناسب برای استفاده در دستور SQL قرار بگیرد.
برای مثال اگر نام دیتابیس:

Company Data

باشد، استفاده مستقیم از آن می‌تواند مشکل ایجاد کند؛ اما QUOTENAME آن را به شکل مناسب تبدیل می‌کند.

جستجوی جدول در Schema خاص در SQL Server

ممکن است در یک دیتابیس چند جدول با نام مشابه اما Schema متفاوت وجود داشته باشد.
برای مثال:

dbo.Users
admin.Users

در این شرایط بهتر است علاوه بر نام جدول، Schema را نیز بررسی کنیم.
نمونه:

SELECT name
FROM sys.tables
WHERE name = 'Users'
AND SCHEMA_NAME(schema_id) = 'dbo';

در اسکریپت جستجوی چند دیتابیس نیز می‌توان همین شرط را اضافه کرد تا فقط جدول Users از Schema مربوط به dbo پیدا شود.

نکته مهم درباره دیتابیس‌های سیستمی

در SQL Server تعدادی دیتابیس سیستمی مانند master، model، msdb و tempdb وجود دارد.
معمولاً هنگام جستجوی جداول برنامه‌های کاربردی نیازی به بررسی این دیتابیس‌ها نداریم.
به همین دلیل در اسکریپت از شرط:

database_id > 4

استفاده شده است.
البته این شرط به نیاز پروژه بستگی دارد و در بعضی سناریوها ممکن است بخواهید دیتابیس‌های سیستمی را نیز بررسی کنید.

جستجوی بخشی از نام جدول در SQL Server

گاهی نام دقیق جدول را نمی‌دانیم و فقط بخشی از نام آن را می‌دانیم.
در این شرایط می‌توان از LIKE استفاده کرد.
مثلاً برای پیدا کردن جدول‌هایی که کلمه User در نام آن‌ها وجود دارد:

SELECT name
FROM sys.tables
WHERE name LIKE '%User%';

این روش برای زمانی مفید است که در یک پروژه جدول‌هایی مانند:

• Users
• UserRoles
• UserAccounts
• SystemUsers

وجود داشته باشند.

نکات مهم هنگام اجرای اسکریپت

اگر تعداد دیتابیس‌های SQL Server زیاد باشد، اجرای اسکریپت ممکن است کمی زمان ببرد.
همچنین کاربر باید دسترسی لازم برای مشاهده اطلاعات دیتابیس‌های موردنظر را داشته باشد.
از طرف دیگر، بهتر است اسکریپت را روی محیط عملیاتی با دقت اجرا کنید؛ زیرا هدف این اسکریپت فقط خواندن اطلاعات ساختاری است و نباید دستورات تغییر‌دهنده مانند UPDATE، DELETE یا DROP در آن قرار داده شود.