اسکریپت لیست Temporal Table ها در یک دیتابیس

اسکریپت SQL Server برای لیست Temporal Table ها در یک دیتابیس

در SQL Server، جدول‌های Temporal یا System-Versioned Temporal Tables قابلیت نگهداری تاریخچه تغییرات داده‌ها را دارند.
با استفاده از این قابلیت، می‌توان وضعیت فعلی و نسخه‌های قبلی رکوردها را در بازه‌های زمانی مختلف بررسی کرد.
اگر هدف شما تهیه یک اسکریپت برای شناسایی تمام Temporal Tableهای موجود در دیتابیس است، می‌توانید از کد زیر استفاده کنید.

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

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

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

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

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

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

 اسکریپت اصلی برای لیست تمام Temporal Tableها

SELECT
    s.name AS SchemaName,
    t.name AS TemporalTableName,
    t.temporal_type,
    CASE t.temporal_type
        WHEN 0 THEN 'Non-Temporal Table'
        WHEN 1 THEN 'History Table'
        WHEN 2 THEN 'System-Versioned Temporal Table'
    END AS TemporalTypeDescription,
    hs.name AS HistorySchemaName,
    ht.name AS HistoryTableName,
    t.create_date,
    t.modify_date
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
LEFT JOIN sys.tables AS ht
    ON t.history_table_id = ht.object_id
LEFT JOIN sys.schemas AS hs
    ON ht.schema_id = hs.schema_id
WHERE t.temporal_type = 2
ORDER BY
    s.name,
    t.name;

این اسکریپت تمام جدول‌هایی را که در دیتابیس فعلی به‌صورت System-Versioned Temporal تعریف شده‌اند، نمایش می‌دهد.

توضیح ستون‌های خروجی

• SchemaName

  نام اسکیمای جدول، مانند dbo.

• TemporalTableName

  نام جدول اصلی که قابلیت Temporal برای آن فعال است.

• temporal_type

  نوع جدول از نظر قابلیت Temporal.

• TemporalTypeDescription

  توضیح متنی نوع جدول.

• HistorySchemaName

  نام اسکیمای جدول تاریخچه.

• HistoryTableName

  نام جدول تاریخچه مرتبط با جدول اصلی.

• create_date

  تاریخ ایجاد جدول.

• modify_date

  تاریخ آخرین تغییر ثبت‌شده در متادیتای جدول.

نکته مهم این است که شرط WHERE t.temporal_type = 2 باعث می‌شود فقط جدول‌های اصلی Temporal نمایش داده شوند، نه جدول‌های تاریخچه یا جدول‌های عادی.

تفاوت انواع Temporal Table

در SQL Server، ستون temporal_type در نمای سیستمی sys.tables سه مقدار اصلی دارد:

مقدار	نوع جدول	توضیح
0	Non-Temporal Table	جدول عادی
1	History Table	جدول نگهداری تاریخچه
2	System-Versioned Temporal Table	جدول اصلی با قابلیت نسخه‌بندی زمانی

برای مثال، اگر جدول Employees به‌صورت Temporal تعریف شده باشد، ممکن است تاریخچه آن در جدول EmployeesHistory ذخیره شود.
در این حالت، جدول اصلی اطلاعات فعلی را نگهداری می‌کند و جدول تاریخچه نسخه‌های قدیمی رکوردها را ثبت می‌کند.

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

اگر بخواهید علاوه بر جدول‌های اصلی، جدول‌های تاریخچه را نیز مشاهده کنید، می‌توانید از این نسخه استفاده کنید:

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    t.temporal_type,
    CASE t.temporal_type
        WHEN 0 THEN 'Regular Table'
        WHEN 1 THEN 'History Table'
        WHEN 2 THEN 'Temporal Main Table'
    END AS TableType,
    t.history_table_id
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
WHERE t.temporal_type IN (1, 2)
ORDER BY
    t.temporal_type,
    s.name,
    t.name;

 

این کوئری هر دو گروه زیر را نمایش می‌دهد:
1. جدول‌های اصلی که قابلیت System Versioning برای آن‌ها فعال است.
2. جدول‌های تاریخچه‌ای که SQL Server برای نگهداری نسخه‌های قبلی استفاده می‌کند.
توجه داشته باشید که هر جدول تاریخچه لزوماً نامی مشابه جدول اصلی ندارد؛ بنابراین بهتر است برای یافتن ارتباط دقیق بین جدول اصلی و جدول تاریخچه از history_table_id یا history_table_id در متادیتای مرتبط استفاده کنید.

نمایش اطلاعات دوره زمانی در Temporal Tableها

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

• ValidFrom: زمان شروع اعتبار نسخه رکورد.
• ValidTo: زمان پایان اعتبار نسخه رکورد.

نام این ستون‌ها می‌تواند متفاوت باشد.
برای مشاهده نام واقعی ستون‌های دوره زمانی در جدول‌های Temporal می‌توانید کوئری زیر را اجرا کنید:

SELECT
    s.name AS SchemaName,
    t.name AS TemporalTableName,
    c1.name AS ValidFromColumn,
    c2.name AS ValidToColumn
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
LEFT JOIN sys.periods AS p
    ON t.object_id = p.object_id
LEFT JOIN sys.columns AS c1
    ON p.object_id = c1.object_id
    AND p.start_column_id = c1.column_id
LEFT JOIN sys.columns AS c2
    ON p.object_id = c2.object_id
    AND p.end_column_id = c2.column_id
WHERE t.temporal_type = 2
ORDER BY
    s.name,
    t.name;

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

نکات مهم هنگام استفاده

اول: این کوئری‌ها را در دیتابیسی اجرا کنید که قصد بررسی آن را دارید.
اگر لازم است دیتابیس مشخصی انتخاب شود، می‌توانید ابتدا دستور زیر را اجرا کنید:

USE YourDatabaseName;
GO

نام YourDatabaseName را با نام دیتابیس خود جایگزین کنید.
دوم: برای مشاهده اطلاعات، معمولاً دسترسی مناسب به متادیتای دیتابیس لازم است.
اگر برخی جدول‌ها در خروجی ظاهر نشوند، سطح دسترسی کاربر را بررسی کنید.
سوم: این اسکریپت‌ها صرفاً اطلاعات ساختاری را می‌خوانند و هیچ جدولی ایجاد، حذف یا تغییر نمی‌دهند.
چهارم: جدول تاریخچه می‌تواند به‌صورت خودکار توسط SQL Server ایجاد شده باشد یا هنگام تعریف Temporal Table به‌طور صریح مشخص شده باشد. در نتیجه، نباید فقط بر اساس نام جدول فرض کنید که آن جدول، تاریخچه یک جدول خاص است.

جمع‌بندی

اگر تنها به فهرست جدول‌های اصلی Temporal در دیتابیس نیاز دارید، اسکریپت بخش اول کافی است.
اگر هدف شما بررسی کامل ساختار Temporal Tableها، یافتن جدول‌های تاریخچه و شناسایی ستون‌های زمانی است، می‌توانید هر سه اسکریپت را اجرا کنید.
این روش از متادیتای رسمی SQL Server استفاده می‌کند و برای تهیه گزارش، بررسی ساختار دیتابیس و مستندسازی جدول‌های Temporal مناسب است.