کاوش فراداده SQL Server با Information Schema Views

کاوش در فراداده پایگاه داده: آشنایی با Information Schema Views در SQL Server

در دنیای پایگاه‌های داده، دسترسی به فراداده (metadata) یا اطلاعاتی درباره ساختار پایگاه داده، از اهمیت بالایی برخوردار است. SQL Server مجموعه‌ای از ویوهای سیستمی ارائه می‌دهد که به شما امکان می‌دهد این اطلاعات را بازیابی کنید. یکی از مهم‌ترین این مجموعه‌ها، `INFORMATION_SCHEMA` است که یک راه استاندارد و سازگار با SQL برای دسترسی به فراداده فراهم می‌کند. این بخش شامل چندین ویو است که هر کدام اطلاعات خاصی را در مورد اجزای پایگاه داده در اختیار می‌گذارند.

INFORMATION_SCHEMA.VIEWS

این ویو اطلاعات مربوط به تمام ویوها (نماها) در پایگاه داده را نمایش می‌دهد. با استفاده از آن می‌توانید نام ویوها، کاتالوگ (نام پایگاه داده) و اسکیما (مالک ویو) را مشاهده کنید.

با اجرای این کوئری، می‌توانید تمام ویوهای موجود در پایگاه داده خود را مشاهده کنید:

SELECT *
FROM INFORMATION_SCHEMA.VIEWS;

INFORMATION_SCHEMA.TABLES

این ویو اطلاعات مفیدی درباره تمام جداول و ویوهای سیستمی در پایگاه داده فعلی را فراهم می‌کند. می‌توانید از آن برای شناسایی جداول اصلی (base tables) و ویوها استفاده کنید.

برای مشاهده تمام جداول و ویوها:

SELECT *
FROM INFORMATION_SCHEMA.TABLES;

برای فیلتر کردن و نمایش فقط جداول اصلی:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

INFORMATION_SCHEMA.COLUMNS

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

برای دیدن اطلاعات تمام ستون‌ها:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS;

برای مشاهده ستون‌های یک جدول خاص، مثلاً جدول ‘Product’:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Product';

برای پیدا کردن تمام ستون‌هایی که Nullable هستند (می‌توانند مقادیر Null داشته باشند):

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES';

INFORMATION_SCHEMA.ROUTINES

این ویو اطلاعات مربوط به توابع و روال‌های ذخیره‌شده (stored procedures) در پایگاه داده را بازیابی می‌کند. می‌توانید جزئیاتی مانند نام روال، اسکیما و نوع آن (تابع یا روال ذخیره‌شده) را مشاهده کنید.

برای مشاهده تمام روال‌ها (توابع و روال‌های ذخیره‌شده):

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES;

برای فیلتر کردن و نمایش فقط روال‌های ذخیره‌شده:

SELECT ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

INFORMATION_SCHEMA.PARAMETERS

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

برای مشاهده تمام پارامترهای توابع و روال‌ها:

SELECT *
FROM INFORMATION_SCHEMA.PARAMETERS;

برای یافتن پارامترهای یک روال خاص، مثلاً روال ‘uspGetErrorInfo’:

SELECT *
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = 'uspGetErrorInfo';

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

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

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

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;

برای یافتن ستون‌های کلید برای یک جدول خاص، مانند ‘Product’:

SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'Product';

INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

این ویو اطلاعات مربوط به جداولی را که توسط محدودیت‌ها (constraints) ارجاع داده شده‌اند، فراهم می‌کند. این ویو به شما کمک می‌کند تا وابستگی‌های جداول به محدودیت‌ها را درک کنید.

برای مشاهده تمام جداول استفاده‌شده در محدودیت‌ها:

SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE;

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

این ویو اطلاعات مربوط به ستون‌هایی را که توسط محدودیت‌ها (مانند چک، پیش‌فرض، کلید اصلی و کلید خارجی) استفاده می‌شوند، ارائه می‌دهد.

برای مشاهده تمام ستون‌های استفاده‌شده در محدودیت‌ها:

SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE;

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

این ویو اطلاعات مربوط به تمام محدودیت‌های جدول (مانند PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) را نمایش می‌دهد. این ویو برای شناسایی نوع محدودیت‌های اعمال شده بر روی جداول کاربردی است.

برای مشاهده تمام محدودیت‌های جدول:

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

برای فیلتر کردن و نمایش فقط محدودیت‌های کلید اصلی (PRIMARY KEY):

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY';