راهنمای جامع توابع متادیتا در SQL Server؛ از کشف ساختار تا ممیزی و بهینهسازی
مقدمه؛ چرا Metadata ستون فقرات ابزارهای حرفهای است؟
متادیتا در SQL Server اطلاعاتی درباره دادهها، اشیا، ستونها، نوعها، ایندکسها، فایلها و Context اتصال است. برنامههای مدیریتی، مولدهای گزارش، Migrationها، سامانههای ممیزی و ابزارهای مانیتورینگ بدون یک لایه متادیتای دقیق ناچار میشوند نامها و شناسهها را Hard-code کنند؛ روشی که در نخستین تغییر Schema یا انتقال میان محیطها شکننده میشود.
توابع این مجموعه مسیر کوتاهی برای تبدیل نام به شناسه، شناسه به نام یا خواندن یک property مشخص ارائه میکنند. بااینحال، خروجی آنها فقط زمانی درست تفسیر میشود که Context پایگاه داده، Schema، نوع شیء و Metadata Visibility معلوم باشد. NULL همیشه به معنی «وجود ندارد» نیست؛ ممکن است حساب اجراکننده اجازه مشاهده شیء را نداشته باشد.
این مقاله چهارده تابع APP_NAME، DB_ID، DB_NAME، OBJECT_ID، OBJECT_NAME، COL_NAME، COLUMNPROPERTY، FILE_ID، FILE_NAME، INDEXPROPERTY، OBJECTPROPERTY، OBJECTPROPERTYEX، TYPE_NAME و TYPE_ID را دستهبندی و مقایسه میکند. هر مورد پیوندی به راهنمای مستقل با ده مثال، خروجی نمونه، خطاهای رایج، Performance، FAQ و سؤال مصاحبه دارد.
دسترسی سریع به آموزش هر تابع
مدل ذهنی صحیح برای کار با متادیتا
پیش از اجرای تابع، سه محور را مشخص کنید: Context، Identity و Visibility. Context تعیین میکند شناسه در کدام پایگاه معنا دارد؛ Identity رابطه میان نام منطقی و شناسه داخلی را بیان میکند؛ Visibility مشخص میکند حساب جاری کدام متادیتا را میتواند ببیند. بیشتر خطاهای عملی زمانی رخ میدهند که یکی از این سه محور نادیده گرفته شود.
شناسههای object_id، database_id، file_id و user_type_id کلیدهای اتصال در کاتالوگ هستند، اما قرارداد پایدار میان سرورها محسوب نمیشوند. کد قابل حمل باید نام معتبر را در Context مقصد حل کند و شناسه همان محیط را به دست آورد. برعکس، در گزارشهای حجیم بهتر است شناسه یکبار حل شود و Joinها و Predicateها بر اساس مقدار عددی اجرا شوند.
قاعده طلایی: نام را برای مرز انسانی و پیکربندی نگه دارید، شناسه را برای اتصال و فیلتر داخلی همان Context استفاده کنید و هیچکدام را بدون کنترل مجوز و NULL معتبر فرض نکنید.
دستهبندی منطقی توابع مجموعه
۱. Context اتصال و پایگاه داده
APP_NAME برچسب برنامه نشست را نشان میدهد و DB_ID و DB_NAME میان نام و شناسه پایگاه داده تبدیل انجام میدهند. این گروه برای ثبت Context اجرای Job، خواناسازی DMVها و تفکیک بارهای کاری مفید است؛ ولی APP_NAME قابل تنظیم توسط کلاینت است و نباید نقش احراز هویت داشته باشد.
۲. اشیای Schema-scoped
OBJECT_ID و OBJECT_NAME نام و شناسه اشیا را به یکدیگر تبدیل میکنند. OBJECTPROPERTY و OBJECTPROPERTYEX ویژگیهای اشیا را میخوانند. نام Schema-qualified، پایگاه جاری و مجوز مشاهده متادیتا در تمام این عملیات اهمیت مستقیم دارد.
۳. ستونها و قرارداد Schema
COL_NAME نام ستون را از شناسه شیء و ستون میسازد و COLUMNPROPERTY ویژگیهایی مانند Identity، Computed یا قابلیت NULL را بررسی میکند. برای گزارش انبوه و جزئیات بیشتر، این توابع در کنار sys.columns و sys.types استفاده میشوند.
۴. فایلها و ذخیرهسازی
FILE_ID و FILE_NAME میان نام منطقی فایل و شناسه آن در پایگاه جاری تبدیل میکنند. نام منطقی با physical_name متفاوت است. گزارش ظرفیت، رشد فایل و عملیات نگهداری باید این تفاوت را صریح رعایت کنند.
۵. ایندکس و Statistics
INDEXPROPERTY ویژگی ایندکس یا Statistics مشخص را برمیگرداند. برای ممیزی یک ویژگی تابع خواناست، اما برای تحلیل همه ایندکسها ستونهای sys.indexes، sys.index_columns و نماهای آمار استفاده، جزئیات و کارایی بیشتری فراهم میکنند.
۶. انواع داده
TYPE_ID و TYPE_NAME نام و شناسه نوع داده را تبدیل میکنند. user_type_id میتواند Alias Type را حفظ کند، درحالیکه system_type_id به نوع پایه اشاره دارد. تولید DDL باید طول، Precision، Scale و Collation را جداگانه از کاتالوگ بخواند.
متادیتا در طراحی تاریخ و زمان، UTC و Offset
اگرچه توابع این مجموعه محاسبه تاریخ انجام نمیدهند، برای کشف و اعتبارسنجی ستونهای زمانی حیاتیاند. TYPE_NAME نوعهای date، time، datetime2 و datetimeoffset را مشخص میکند؛ sys.columns دقت و Scale را میدهد و COLUMNPROPERTY برخی ویژگیهای ستون را تکمیل میکند. datetime2 برای زمان بدون Offset و datetimeoffset برای نگهداری زمان همراه Offset طراحی شده است.
در سامانه چندمنطقهای، زمان رویداد معمولاً به UTC ثبت و منطقه یا Offset مورد نیاز جداگانه مدیریت میشود. زمان محلی سرور، UTC و Offset یک مفهوم واحد نیستند. ابزار Metadata-driven میتواند ستونهای زمانی نامعتبر، دقت ناسازگار یا استفاده ناخواسته از datetime قدیمی را پیش از Deployment گزارش کند.
توابع دریافت زمان جاری، محاسبه و اختلاف تاریخ، استخراج جزء، ساخت تاریخ، مدیریت Offset و اعتبارسنجی تاریخ خانوادههای مستقلی از توابع T-SQL هستند. متادیتای این مقاله لایه کنترل ساختار آنهاست: ابتدا نوع و ویژگی ستون را کشف میکند و سپس Query زمانی روی قرارداد درست اجرا میشود.
جدول مقایسهای چهارده تابع
| تابع | کاربرد اصلی | نوع خروجی یا نکته مهم | لینک آموزش کامل |
|---|
| APP_NAME | شناسایی نام برنامه کلاینتی که نشست جاری را ایجاد کرده است | nvarchar(128)؛ NULL و Metadata Visibility را کنترل کنید | راهنمای APP_NAME |
| DB_ID | تبدیل نام پایگاه داده به شناسه داخلی آن در نمونه SQL Server | int؛ NULL و Metadata Visibility را کنترل کنید | راهنمای DB_ID |
| DB_NAME | تبدیل شناسه داخلی پایگاه داده به نام قابل خواندن | nvarchar(128)؛ NULL و Metadata Visibility را کنترل کنید | راهنمای DB_NAME |
| OBJECT_ID | یافتن شناسه داخلی یک شیء Schema-scoped بر پایه نام آن | int؛ NULL و Metadata Visibility را کنترل کنید | راهنمای OBJECT_ID |
| OBJECT_NAME | تبدیل شناسه داخلی شیء به نام قابل خواندن آن | sysname؛ NULL و Metadata Visibility را کنترل کنید | راهنمای OBJECT_NAME |
| COL_NAME | بازیابی نام ستون بر پایه شناسه شیء و شناسه ستون | sysname؛ NULL و Metadata Visibility را کنترل کنید | راهنمای COL_NAME |
| COLUMNPROPERTY | خواندن یک ویژگی مشخص از ستون یا پارامتر با خروجی عددی | int؛ NULL و Metadata Visibility را کنترل کنید | راهنمای COLUMNPROPERTY |
| FILE_ID | تبدیل نام منطقی فایل پایگاه داده جاری به شناسه فایل | smallint؛ NULL و Metadata Visibility را کنترل کنید | راهنمای FILE_ID |
| FILE_NAME | تبدیل شناسه فایل به نام منطقی قابل خواندن | nvarchar(128)؛ NULL و Metadata Visibility را کنترل کنید | راهنمای FILE_NAME |
| INDEXPROPERTY | خواندن ویژگی عددی یک ایندکس یا آمار مشخص | int؛ NULL و Metadata Visibility را کنترل کنید | راهنمای INDEXPROPERTY |
| OBJECTPROPERTY | بررسی یک ویژگی عددی درباره شیء در Context پایگاه داده جاری | int؛ NULL و Metadata Visibility را کنترل کنید | راهنمای OBJECTPROPERTY |
| OBJECTPROPERTYEX | دریافت دامنه وسیعتری از ویژگیهای شیء با خروجی sql_variant | sql_variant؛ NULL و Metadata Visibility را کنترل کنید | راهنمای OBJECTPROPERTYEX |
| TYPE_NAME | تبدیل شناسه نوع داده به نام قابل خواندن آن | nvarchar(128)؛ NULL و Metadata Visibility را کنترل کنید | راهنمای TYPE_NAME |
| TYPE_ID | تبدیل نام نوع داده به شناسه داخلی آن | int؛ NULL و Metadata Visibility را کنترل کنید | راهنمای TYPE_ID |
شش مثال ترکیبی و کاربردی
مثال ترکیبی 1: کنترل Context و برنامه متصل
این مثال نشان میدهد چند قطعه متادیتا چگونه در یک سناریوی اجرایی کنار هم قرار میگیرند. Query را ابتدا در محیط آزمایشی و با Context کنترلشده اجرا کنید.
SELECT DB_ID() AS DatabaseId, DB_NAME() AS DatabaseName, APP_NAME() AS ApplicationName;
| فیلد یا ستون | خروجی نمونه |
|---|
| DatabaseId | 5 |
| DatabaseName | a00b |
| ApplicationName | OrderApi |
نکته: این خروجی سرآغاز مناسب هر لاگ عیبیابی متادیتا است. خروجی نمونه است و مقدار واقعی باید از سرور مقصد خوانده شود.
مثال ترکیبی 2: ساخت فرهنگ ستونهای جدول
این مثال نشان میدهد چند قطعه متادیتا چگونه در یک سناریوی اجرایی کنار هم قرار میگیرند. Query را ابتدا در محیط آزمایشی و با Context کنترلشده اجرا کنید.
DECLARE @ObjectId int=OBJECT_ID(N'dbo.tblNewsContent',N'U');
SELECT c.column_id,COL_NAME(@ObjectId,c.column_id) AS ColumnName,TYPE_NAME(c.user_type_id) AS TypeName
FROM sys.columns AS c WHERE c.object_id=@ObjectId;
| فیلد یا ستون | خروجی نمونه |
|---|
| ColumnName | NewsID |
| TypeName | int |
نکته: سه خانواده Object، Column و Type در یک گزارش ترکیب شدهاند. خروجی نمونه است و مقدار واقعی باید از سرور مقصد خوانده شود.
مثال ترکیبی 3: ممیزی ویژگی کلید و ستون
این مثال نشان میدهد چند قطعه متادیتا چگونه در یک سناریوی اجرایی کنار هم قرار میگیرند. Query را ابتدا در محیط آزمایشی و با Context کنترلشده اجرا کنید.
DECLARE @ObjectId int=OBJECT_ID(N'dbo.tblNewsContent',N'U');
SELECT OBJECTPROPERTYEX(@ObjectId,'TableHasPrimaryKey') AS HasPrimaryKey,
COLUMNPROPERTY(@ObjectId,N'NewsID','IsIdentity') AS NewsIdIsIdentity;
| فیلد یا ستون | خروجی نمونه |
|---|
| HasPrimaryKey | 1 |
| NewsIdIsIdentity | 1 |
نکته: خروجیها باید سهحالته تحلیل شوند. خروجی نمونه است و مقدار واقعی باید از سرور مقصد خوانده شود.
مثال ترکیبی 4: گزارش فایلهای پایگاه جاری
این مثال نشان میدهد چند قطعه متادیتا چگونه در یک سناریوی اجرایی کنار هم قرار میگیرند. Query را ابتدا در محیط آزمایشی و با Context کنترلشده اجرا کنید.
SELECT file_id,FILE_NAME(file_id) AS LogicalName,size*8.0/1024 AS SizeMB
FROM sys.database_files ORDER BY file_id;
| فیلد یا ستون | خروجی نمونه |
|---|
| LogicalName | a00b |
| SizeMB | 512.000000 |
نکته: نام منطقی با مسیر فیزیکی متفاوت است. خروجی نمونه است و مقدار واقعی باید از سرور مقصد خوانده شود.
مثال ترکیبی 5: ممیزی ایندکسهای جدول
این مثال نشان میدهد چند قطعه متادیتا چگونه در یک سناریوی اجرایی کنار هم قرار میگیرند. Query را ابتدا در محیط آزمایشی و با Context کنترلشده اجرا کنید.
DECLARE @ObjectId int=OBJECT_ID(N'dbo.tblNewsContent',N'U');
SELECT i.name,INDEXPROPERTY(@ObjectId,i.name,'IsUnique') AS IsUnique,
INDEXPROPERTY(@ObjectId,i.name,'IsDisabled') AS IsDisabled
FROM sys.indexes AS i WHERE i.object_id=@ObjectId AND i.index_id>0;
| فیلد یا ستون | خروجی نمونه |
|---|
| name | PK_tblNewsContent |
| IsUnique | 1 |
| IsDisabled | 0 |
نکته: برای خروجی انبوه ستونهای sys.indexes را نیز مقایسه کنید. خروجی نمونه است و مقدار واقعی باید از سرور مقصد خوانده شود.
مثال ترکیبی 6: اعتبارسنجی نوع و شیء پیش از Deployment
این مثال نشان میدهد چند قطعه متادیتا چگونه در یک سناریوی اجرایی کنار هم قرار میگیرند. Query را ابتدا در محیط آزمایشی و با Context کنترلشده اجرا کنید.
IF OBJECT_ID(N'dbo.tblNewsContent',N'U') IS NULL
THROW 50020,N'جدول هدف موجود یا قابل مشاهده نیست.',1;
IF TYPE_ID(N'nvarchar') IS NULL
THROW 50021,N'نوع داده مورد انتظار در دسترس نیست.',1;
SELECT N'اعتبارسنجی موفق بود' AS Status;
| فیلد یا ستون | خروجی نمونه |
|---|
| Status | اعتبارسنجی موفق بود |
نکته: Deployment باید با خطای روشن متوقف شود، نه اینکه با فرض نادرست ادامه یابد. خروجی نمونه است و مقدار واقعی باید از سرور مقصد خوانده شود.
امنیت، مجوز مشاهده متادیتا و SQL پویا
SQL Server نمایش متادیتا را به مالکیت و مجوز Securableها محدود میکند. به همین دلیل دو کاربر ممکن است با Query یکسان پاسخ متفاوت بگیرند. دادن مجوز گسترده صرفاً برای رفع NULL راه درستی نیست؛ نیاز واقعی را تعیین کنید و VIEW DEFINITION یا مجوز دقیقتر را در کوچکترین دامنه لازم اعطا نمایید.
خروجی متادیتا گاهی برای ساخت SQL پویا مصرف میشود. در این حالت نام پایگاه، Schema، جدول، ستون و نوع باید از کاتالوگ یا فهرست مجاز بیاید و با QUOTENAME محصور شود. مقدارهای داده را با sp_executesql پارامتری کنید. QUOTENAME جای پارامتر داده را نمیگیرد و پارامتر نیز جای Identifier را نمیگیرد.
APP_NAME، HOST_NAME و برچسبهای اتصال برای مشاهدهپذیری مفیدند، اما ورودی قابل کنترل کلاینت هستند. کنترل دسترسی باید بر Login، User، Role، Permission و سیاست امنیتی معتبر تکیه کند. گزارش ممیزی نیز بهتر است زمان، session_id، original_login و Context پایگاه را کنار برچسب برنامه ثبت کند.
راهبرد Performance و انتخاب تابع یا نمای sys
توابع متادیتا برای Lookup منفرد ساده و خوانا هستند. وقتی گزارش هزاران شیء و چندین property را بررسی میکند، اجرای تابع در هر ردیف میتواند تکرار غیرضروری ایجاد کند. در آن وضعیت، Join مستقیم sys.objects، sys.columns، sys.types، sys.indexes و کاتالوگ فایلها معمولاً Plan قابل فهمتری دارد.
بهینهسازی باید با اندازهگیری انجام شود. نسخه تابعی و نسخه Join را با STATISTICS IO، STATISTICS TIME و Actual Execution Plan مقایسه کنید. تعداد ردیف، مجوز حساب، Cache و بار همزمان باید یکسان باشد. نتیجه آزمایش کوچک در Development را بدون بازآزمایی به Production تعمیم ندهید.
- Lookup ثابت را یکبار در متغیر محاسبه کنید.
- فیلتر را در صورت امکان روی شناسه موجود کاتالوگ اعمال کنید.
- برای نام کامل، Schema و نام شیء را جداگانه و امن ترکیب کنید.
- از SELECT گسترده و بدون فیلتر روی کاتالوگ سروری پرهیز کنید.
- پس از ارتقای نسخه یا تغییر مجوز، تست بازگشت متادیتا را تکرار کنید.
سؤالات متداول
۱. متادیتا در SQL Server چیست؟
اطلاعاتی است که ساختار و ویژگیهای پایگاه، اشیا، ستونها، انواع، ایندکسها و فایلها را توصیف میکند. این اطلاعات از توابع داخلی و نماهای کاتالوگ قابل خواندن است.
۲. چرا یک تابع Metadata مقدار NULL میدهد؟
ورودی نامعتبر، Context اشتباه، property ناسازگار یا نبود مجوز مشاهده متادیتا دلایل اصلی هستند. NULL بهتنهایی نبود قطعی شیء را ثابت نمیکند.
۳. آیا ساخت ابزار Metadata-driven از نظر تجاری مفید است؟
بله؛ اتوماسیون ممیزی، تولید گزارش و Migration دوبارهکاری و خطای استقرار را کاهش میدهد. ارزش واقعی زمانی ایجاد میشود که تست، مجوز و ثبت خطا نیز بخشی از راهکار باشند.
۴. آموزش این توابع برای تیم توسعه چه بازدهی دارد؟
تیم از Hard-code کردن شناسهها فاصله میگیرد، علت NULL را درست تحلیل میکند و Queryهای قابل حملتری مینویسد. آموزش پروژهمحور با Schema واقعی بیشترین اثر را دارد.
۵. تابع بهتر است یا نمای sys؟
برای Lookup منفرد تابع سادهتر است؛ برای گزارش انبوه و چندویژگی، نماهای sys انعطاف و جزئیات بیشتری دارند. تصمیم نهایی با اندازهگیری Plan و IO گرفته میشود.
۶. آیا میتوان ابزار ممیزی Schema اختصاصی سفارش داد؟
بله؛ دامنه اشیا، قواعد نامگذاری، نسخه SQL Server، خروجی و مدل مجوز ابتدا مشخص و سپس ابزار همراه با تست و مستندات پیادهسازی میشود.
۷. رایجترین خطای کار با شناسههای متادیتا چیست؟
Hard-code کردن object_id یا user_type_id و انتقال آن به محیط دیگر است. شناسه باید در همان پایگاه مقصد و هنگام اجرا حل شود.
۸. چگونه Performance گزارش متادیتا بهتر میشود؟
Lookupهای ثابت یکبار محاسبه، Predicateها روی شناسهها اعمال و برای مجموعه بزرگ از Join مستقیم کاتالوگ استفاده میشود. سپس IO و Plan قبل و بعد مقایسه میگردد.
۹. Best Practice امنیتی چیست؟
اصل حداقل دسترسی، تفکیک Metadata Visibility از مجوز داده و ایمنسازی SQL پویا با QUOTENAME و پارامترها باید همزمان رعایت شوند.
۱۰. سازگاری نسخهها چگونه کنترل میشود؟
فهرست propertyها و پلتفرمهای پشتیبانیشده را در مستندات نسخه هدف بررسی کنید و تست یکپارچه را روی SQL Server یا Azure SQL مقصد اجرا نمایید.
سؤالات مصاحبه و چکلیست نهایی
چرا NULL در Metadata پاسخ دوحالته نیست؟
زیرا نبود مجوز، ورودی نامعتبر و نبود واقعی شیء میتوانند نتیجه مشابه بسازند و هرکدام مسیر عیبیابی متفاوت دارند.
چگونه نام کامل شیء را امن تولید میکنید؟
نام Schema و شیء از کاتالوگ معتبر خوانده و هر بخش جداگانه با QUOTENAME محصور میشود؛ مقدارهای داده نیز پارامتری باقی میمانند.
چه زمانی شناسه را در متغیر نگه میدارید؟
وقتی یک نام ثابت چند بار در Query استفاده میشود، حل یکباره شناسه تکرار را کم و قصد کد را روشن میکند.
- پایگاه داده و Schema مقصد صریحاند.
- تمام NULLها مسیر تشخیصی دارند.
- شناسهای میان محیطها Hard-code نشده است.
- مجوز مشاهده متادیتا حداقلی و مستند است.
- نامهای SQL پویا با QUOTENAME محصور شدهاند.
- نسخه تابعی و Join برای گزارش حجیم مقایسه شدهاند.
- تست تغییر Schema و ارتقای نسخه وجود دارد.
- خروجی ابزار زمان، Context و هویت اجرا را ثبت میکند.
جمعبندی و مسیر مطالعه
توابع Metadata پلی میان نامهای قابل فهم و شناسهها و ویژگیهای داخلی SQL Server هستند. استفاده درست از آنها ابزارهای قابل حمل، گزارشهای خوانا و Deploymentهای امنتر میسازد. استفاده نادرست، بهویژه Hard-code کردن شناسه، بیتوجهی به Schema و تفسیر سادهانگارانه NULL، نتیجهای شکننده تولید میکند.
برای تسلط عملی، مقاله هر تابع را با مثالهای مستقل اجرا کنید و سپس همان الگو را روی Schema آزمایشی سازمان خود تطبیق دهید. پیوندهای زیر مسیر مستقیم مطالعه کامل را ارائه میکنند.