آموزش فراداده SQL Server sys.databases و sys.tables

کشف فراداده SQL Server: راهنمای جامع sys.databases و sys.tables

فراداده SQL Server به داده‌هایی گفته می‌شود که اطلاعاتی درباره پایگاه‌های داده و اشیاء آن‌ها ارائه می‌دهند. این داده‌ها برای فهم ساختار پایگاه داده، نظارت بر تغییرات و حتی تولید پرس‌وجوهای دینامیک SQL کاربرد دارند. کاتالوگ سیستمی SQL Server مجموعه‌ای از viewهاست که دسترسی به این اطلاعات فراداده‌ای را در مورد نمونه‌های SQL Server، پایگاه‌های داده و اشیاء پایگاه داده فراهم می‌کند. در این راهنما، به بررسی برخی از پرکاربردترین viewهای فراداده SQL Server، با تمرکز بر `sys.databases` و `sys.tables` می‌پردازیم و مثال‌هایی از نحوه پرس‌وجو از آن‌ها برای به دست آوردن اطلاعات مختلف در مورد محیط SQL Server شما ارائه می‌دهیم.

**بررسی پایگاه‌های داده با sys.databases**

`sys.databases` یکی از viewهای حیاتی SQL Server است که اطلاعات جامعی در مورد تمام پایگاه‌های داده موجود روی یک نمونه SQL Server ارائه می‌دهد. این view شامل ستون‌هایی مانند `name` (نام پایگاه داده), `database_id` (شناسه پایگاه داده), `create_date` (تاریخ ایجاد) و `state_desc` (وضعیت پایگاه داده) است. با یک پرس‌وجوی ساده می‌توانیم اطلاعات کلیه پایگاه‌های داده را در نمونه فعلی SQL Server استخراج کنیم.

این پرس‌وجو به شما کمک می‌کند تا لیست کامل پایگاه‌های داده را به همراه اطلاعات کلیدی آن‌ها مشاهده کنید:

SELECT
    name,
    database_id,
    create_date,
    state_desc
FROM
    sys.databases;

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

**فیلتر کردن اطلاعات پایگاه داده خاص**

برای دسترسی به اطلاعات یک پایگاه داده خاص، می‌توانید نتایج را فیلتر کنید. به عنوان مثال، برای دریافت اطلاعات مربوط به پایگاه داده `AdventureWorks2019`، از پرس‌وجوی زیر استفاده کنید:

SELECT
    name,
    database_id,
    create_date,
    state_desc
FROM
    sys.databases
WHERE
    name = 'AdventureWorks2019';

این فیلتر به شما اجازه می‌دهد تا به سرعت به جزئیات یک پایگاه داده خاص دسترسی پیدا کنید.

**بررسی سطح سازگاری پایگاه داده**

سطح سازگاری (`compatibility_level`) پایگاه داده یک پارامتر مهم است که نحوه عملکرد پایگاه داده را با نسخه‌های مختلف SQL Server تعیین می‌کند. برای مشاهده این اطلاعات، از ستون `compatibility_level` در `sys.databases` استفاده کنید:

SELECT
    name,
    compatibility_level
FROM
    sys.databases;

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

**بررسی جداول پایگاه داده با sys.tables**

View `sys.tables` منبعی غنی از اطلاعات درباره تمام جداول موجود در پایگاه داده فعلی است. این view شامل ستون‌هایی مانند `name` (نام جدول), `object_id` (شناسه شیء), `create_date` (تاریخ ایجاد) و `modify_date` (تاریخ آخرین تغییر) است. برای دریافت لیست تمام جداول تعریف‌شده توسط کاربر در پایگاه داده فعلی، از `sys.tables` استفاده می‌کنیم:

این پرس‌وجو جداول غیر سیستمی را نمایش می‌دهد که شامل جداول تعریف شده توسط کاربر هستند:

SELECT
    name,
    object_id,
    create_date,
    modify_date
FROM
    sys.tables
WHERE
    is_ms_shipped = 0; -- Exclude system tables

شرط `is_ms_shipped = 0` برای فیلتر کردن جداول سیستمی و تمرکز بر روی جداول کاربری بسیار مهم است.

**فیلتر کردن اطلاعات جدول خاص**

برای جستجوی اطلاعات یک جدول خاص، می‌توانید نتایج را فیلتر کنید. به عنوان مثال، برای دریافت اطلاعات مربوط به جدول `Person.Address` در پایگاه داده `AdventureWorks2019`، می‌توانید پس از تغییر به آن پایگاه داده یا با استفاده از نامگذاری سه‌بخشی، از پرس‌وجوی زیر استفاده کنید:

SELECT
    name,
    object_id,
    create_date,
    modify_date
FROM
    sys.tables
WHERE
    name = 'Address' AND SCHEMA_NAME(schema_id) = 'Person';

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

**ترکیب sys.tables با sys.columns برای ساختار جزئیات جدول**

برای مشاهده جزئیات کامل ساختار یک جدول، می‌توانیم viewهای `sys.tables` و `sys.columns` را به هم متصل (Join) کنیم. `sys.columns` اطلاعات مفصلی درباره تمام ستون‌ها در تمام جداول و viewهای پایگاه داده فعلی ارائه می‌دهد. اکنون، ستون‌های مربوط به جدول `Person.Address` را استخراج می‌کنیم:

این پرس‌وجو با اتصال دو view، نمای کاملی از ستون‌ها، نوع داده‌ها، حداکثر طول و قابلیت null بودن برای هر ستون از جدول مشخص شده را فراهم می‌کند:

SELECT
    t.name AS TableName,
    c.name AS ColumnName,
    TYPE_NAME(c.system_type_id) AS DataType,
    c.max_length AS MaxLength,
    c.is_nullable AS IsNullable
FROM
    sys.tables AS t
INNER JOIN
    sys.columns AS c ON t.object_id = c.object_id
WHERE
    t.name = 'Address' AND SCHEMA_NAME(t.schema_id) = 'Person'
ORDER BY
    ColumnName;

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

**یافتن جداول حاوی ستون‌های خاص**

یکی دیگر از کاربردهای قدرتمند این viewها، یافتن تمام جداولی است که حاوی یک نام ستون خاص هستند. این قابلیت برای تحلیل تأثیر تغییرات (impact analysis) یا فعالیت‌های بازسازی کد (refactoring) بسیار مفید است و به شما کمک می‌کند تا به سرعت مکان‌های استفاده از یک ستون خاص را شناسایی کنید.

برای یافتن تمام جداولی که ستونی با الگوی خاص در نام خود دارند (مثلاً شامل “Name” باشند)، می‌توانید پرس‌وجوی زیر را اجرا کنید:

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName
FROM
    sys.tables AS t
INNER JOIN
    sys.columns AS c ON t.object_id = c.object_id
WHERE
    c.name LIKE '%Name%'
ORDER BY
    SchemaName, TableName, ColumnName;

این پرس‌وجو لیستی از اسامی شمای، جداول و ستون‌های مطابق با الگوی جستجو را برمی‌گرداند.

**شمارش اشیاء در پایگاه داده**

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

SELECT
    COUNT(*) AS NumberOfUserTables
FROM
    sys.tables
WHERE
    is_ms_shipped = 0;

این پرس‌وجو به سرعت تعداد جداول کاربری را محاسبه و نمایش می‌دهد.

به همین ترتیب، برای شمارش کلیه پایگاه‌های داده در نمونه SQL Server، از `sys.databases` استفاده کنید:

SELECT
    COUNT(*) AS NumberOfDatabases
FROM
    sys.databases;

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

**سایر Viewهای مفید فراداده در SQL Server**

در حالی که `sys.databases` و `sys.tables` viewهای بنیادی هستند، SQL Server بسیاری دیگر از viewهای کاتالوگ سیستمی را برای کاوش جامع‌تر فراداده ارائه می‌دهد. در اینجا چند نمونه از این viewهای مفید را معرفی می‌کنیم:
* `sys.columns`: اطلاعاتی درباره ستون‌ها در جداول و viewها ارائه می‌دهد. (ما در مثال ترکیب از آن استفاده کردیم.)
* `sys.views`: اطلاعات مربوط به viewها.
* `sys.procedures`: اطلاعات مربوط به stored procedureها.
* `sys.indexes`: اطلاعات مربوط به ایندکس‌ها.
* `sys.foreign_keys`: اطلاعات مربوط به محدودیت‌های کلید خارجی (Foreign Key).
* `sys.schemas`: اطلاعات مربوط به شمای پایگاه داده (database schemas).