اسکریپت جستجوی جداولی که دارای ستونی با نام مشخص نباشند

اسکریپت جستجوی جداولی که دارای ستونی با نام مشخص نباشند

در پروژه‌های بزرگ دیتابیسی، معمولاً با تعداد زیادی جدول و ستون مواجه هستیم.
زمانی که ساختار دیتابیس پیچیده می‌شود، پیدا کردن جدول‌هایی که یک ستون خاص را ندارند می‌تواند زمان‌بر باشد.
به همین دلیل استفاده از یک اسکریپت جستجوی جداولی که دارای ستونی با نام مشخص نباشند می‌تواند برای مدیران دیتابیس، برنامه‌نویسان و توسعه‌دهندگان بسیار کاربردی باشد.
برای مثال فرض کنید در یک دیتابیس SQL Server صدها جدول وجود دارد و می‌خواهید تمام جدول‌هایی را پیدا کنید که ستونی با نام CreatedDate ندارند.
بررسی تک‌تک جدول‌ها به صورت دستی روش مناسبی نیست.
در این شرایط می‌توان با استفاده از اطلاعات موجود در SQL Server System Catalog یک Query ساده و کاربردی نوشت.

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

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

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

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

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

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

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

در پروژه‌های نرم‌افزاری، ساختار دیتابیس ممکن است در طول زمان تغییر کند.
ممکن است یک ستون به بسیاری از جدول‌ها اضافه شده باشد، اما برخی جدول‌ها همچنان آن ستون را نداشته باشند.
برای مثال، فرض کنید قرار است تمام جدول‌های مربوط به ثبت اطلاعات کاربران دارای ستون زیر باشند:

CreatedDate

اگر دیتابیس شامل ۲۰۰ جدول باشد، بررسی دستی آن‌ها بسیار دشوار خواهد بود.
با یک اسکریپت SQL می‌توانیم به سرعت متوجه شویم کدام جدول‌ها ستون CreatedDate را ندارند.
این موضوع در مواردی مانند بررسی ساختار دیتابیس، Migration، توسعه نرم‌افزار، استانداردسازی جداول و عیب‌یابی دیتابیس اهمیت زیادی دارد.

استفاده از INFORMATION_SCHEMA برای جستجوی ستون‌ها

یکی از روش‌های ساده برای بررسی ساختار جدول‌ها استفاده از INFORMATION_SCHEMA.COLUMNS است.
این View اطلاعات مربوط به ستون‌های موجود در دیتابیس را در اختیار ما قرار می‌دهد.
برای پیدا کردن جدول‌هایی که ستون مشخصی دارند می‌توان Query زیر را نوشت:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT IN
(
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'CreatedDate'
);

در این Query ابتدا تمام جدول‌های واقعی دیتابیس دریافت می‌شوند و سپس جدول‌هایی که در آن‌ها ستونی با نام CreatedDate وجود دارد، از نتیجه حذف می‌شوند.
در نهایت، خروجی شامل جدول‌هایی خواهد بود که ستون CreatedDate را ندارند.

روش پیشنهادی با sys.tables و sys.columns

در SQL Server روش دیگری نیز وجود دارد که معمولاً برای کار با ساختار داخلی دیتابیس بسیار کاربردی است.
SQL Server اطلاعات جداول و ستون‌ها را در Catalog Viewهایی مانند sys.tables و sys.columns نگهداری می‌کند.
برای پیدا کردن جدول‌هایی که ستون مشخصی ندارند، می‌توان از Query زیر استفاده کرد:

SELECT t.name AS TableName
FROM sys.tables AS t
WHERE NOT EXISTS
(
    SELECT 1
    FROM sys.columns AS c
    WHERE c.object_id = t.object_id
      AND c.name = 'CreatedDate'
);

این روش بسیار خوانا است. در واقع Query می‌گوید:
تمام جدول‌ها را پیدا کن که ستونی با نام CreatedDate در آن‌ها وجود ندارد.

بررسی جدول‌های یک Schema خاص

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

SELECT t.name AS TableName
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
AND NOT EXISTS
(
    SELECT 1
    FROM sys.columns AS c
    WHERE c.object_id = t.object_id
      AND c.name = 'CreatedDate'
);

در این حالت فقط جدول‌های موجود در Schema dbo بررسی خواهند شد.
این روش در دیتابیس‌هایی که Schemaهای متعددی دارند بسیار کاربردی است.

جستجوی ستونی با نام دلخواه

یکی از مزیت‌های این اسکریپت این است که می‌توان نام ستون را به راحتی تغییر داد.
برای مثال اگر به دنبال جدول‌هایی هستیم که ستون UserId را ندارند:

SELECT t.name AS TableName
FROM sys.tables AS t
WHERE NOT EXISTS
(
    SELECT 1
    FROM sys.columns AS c
    WHERE c.object_id = t.object_id
      AND c.name = 'UserId'
);

همین Query برای ستون‌هایی مانند موارد زیر نیز قابل استفاده است:

• Id

• Name

• Email

• CreatedAt

• UpdatedAt

• IsActive

• UserId

فقط کافی است مقدار c.name را تغییر دهید.

جستجوی جدول‌هایی که چند ستون مشخص را ندارند

گاهی ممکن است بخواهیم جدول‌هایی را پیدا کنیم که چند ستون خاص را ندارند.
برای مثال فرض کنید انتظار داریم تمام جدول‌ها دارای CreatedDate و UpdatedDate باشند.
می‌توان Query زیر را استفاده کرد:

SELECT t.name AS TableName
FROM sys.tables AS t
WHERE NOT EXISTS
(
    SELECT 1
    FROM sys.columns AS c
    WHERE c.object_id = t.object_id
      AND c.name = 'CreatedDate'
)
OR NOT EXISTS
(
    SELECT 1
    FROM sys.columns AS c
    WHERE c.object_id = t.object_id
      AND c.name = 'UpdatedDate'
);

این Query جدول‌هایی را پیدا می‌کند که حداقل یکی از این دو ستون را ندارند.

نمایش نام Schema و جدول

در دیتابیس‌های بزرگ بهتر است علاوه بر نام جدول، Schema نیز نمایش داده شود تا دقیقاً مشخص باشد ستون موردنظر در کدام جدول وجود ندارد.

SELECT
    s.name AS SchemaName,
    t.name AS TableName
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
WHERE NOT EXISTS
(
    SELECT 1
    FROM sys.columns AS c
    WHERE c.object_id = t.object_id
      AND c.name = 'CreatedDate'
)
ORDER BY s.name, t.name;

خروجی می‌تواند چیزی شبیه این باشد:

SchemaName    TableName
-----------   ----------------
dbo           Customers
dbo           Products
sales         Orders
sales         Invoices

به این ترتیب مشخص می‌شود هر جدول متعلق به کدام Schema است.

تفاوت INFORMATION_SCHEMA و sys.columns

هر دو روش برای بررسی ساختار دیتابیس قابل استفاده هستند، اما در SQL Server معمولاً sys.tables و sys.columns گزینه مناسبی برای دسترسی مستقیم‌تر به Metadata دیتابیس محسوب می‌شوند.
INFORMATION_SCHEMA یک استاندارد عمومی‌تر برای مشاهده اطلاعات ساختاری دیتابیس است، در حالی که Viewهای sys امکانات اختصاصی‌تر SQL Server را در اختیار توسعه‌دهنده قرار می‌دهند.
اگر پروژه شما به صورت اختصاصی روی SQL Server اجرا می‌شود، استفاده از sys.tables و sys.columns می‌تواند انتخاب بسیار مناسبی باشد.

کاربرد اسکریپت جستجوی جداولی که ستونی با نام مشخص نباشند  در Migration

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

CreatedAt DATETIME

پس از اجرای Migration می‌توانید با استفاده از Query بالا بررسی کنید که آیا هنوز جدولی وجود دارد که ستون CreatedAt را ندارد یا خیر.
این کار می‌تواند بخشی از فرآیند کنترل کیفیت و بررسی صحت تغییرات دیتابیس باشد.

نکات مهم هنگام اجرای Query

قبل از اجرای اسکریپت‌های مربوط به ساختار دیتابیس، بهتر است فقط عملیات خواندنی انجام دهید و از Queryهایی که باعث تغییر ساختار جدول‌ها می‌شوند بدون بررسی دقیق استفاده نکنید.
اسکریپت‌های معرفی‌شده در این مقاله صرفاً برای جستجو و بررسی Metadata هستند و ساختار جدول‌ها را تغییر نمی‌دهند.
همچنین در دیتابیس‌های بسیار بزرگ بهتر است Query را روی Schema یا مجموعه جدول‌های موردنظر محدود کنید تا بررسی ساده‌تر و دقیق‌تر باشد.