فراداده ستون SQL Server با INFORMATION_SCHEMACOLUMNS

کاوش عمیق فراداده ستون‌ها در SQL Server با INFORMATION_SCHEMA.COLUMNS

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

برای مشاهده تمام ستون‌های موجود و مشخصات آن‌ها در پایگاه داده فعلی، می‌توانید یک کوئری ساده SELECT را روی INFORMATION_SCHEMA.COLUMNS اجرا کنید:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS;

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

ویوی INFORMATION_SCHEMA.COLUMNS چندین ستون کلیدی را برای ارائه فراداده حیاتی نمایش می‌دهد:

• TABLE_SCHEMA: نام شمای جدول یا ویو که ستون به آن تعلق دارد.

• TABLE_NAME: نام جدول یا ویو که ستون به آن تعلق دارد.

• COLUMN_NAME: نام ستون.

• ORDINAL_POSITION: موقعیت عددی ستون در جدول یا ویو.

• COLUMN_DEFAULT: مقدار پیش‌فرض ستون، در صورت وجود.

• IS_NULLABLE: نشان می‌دهد آیا ستون می‌تواند مقادیر NULL را بپذیرد ('YES' یا 'NO').

• DATA_TYPE: نوع داده ستون (مانند int، varchar، datetime).

• CHARACTER_MAXIMUM_LENGTH: حداکثر طول کاراکتر برای ستون‌های متنی.

• NUMERIC_PRECISION: حداکثر دقت عددی برای ستون‌های عددی.

• NUMERIC_PRECISION_RADIX: پایه مورد استفاده برای تعیین دقت (2 برای داده‌های باینری و 10 برای داده‌های عددی).

• NUMERIC_SCALE: تعداد ارقام سمت راست ممیز اعشار برای ستون‌های عددی.

• DATETIME_PRECISION: دقت ثانیه برای ستون‌های تاریخ و زمان.

• CHARACTER_SET_CATALOG، CHARACTER_SET_SCHEMA، CHARACTER_SET_NAME: اطلاعات مربوط به مجموعه کاراکتر ستون.

• COLLATION_CATALOG، COLLATION_SCHEMA، COLLATION_NAME: اطلاعات مربوط به COLLATE ستون.

• DOMAIN_CATALOG، DOMAIN_SCHEMA، DOMAIN_NAME: اطلاعات مربوط به دامنه تعریف شده توسط کاربر.

اغلب، شما نیاز دارید جزئیات ستون‌ها را برای یک جدول خاص بازیابی کنید. می‌توانید به راحتی نتایج را با استفاده از بندهای WHERE برای TABLE_SCHEMA و TABLE_NAME فیلتر کنید:

SELECT
    COLUMN_NAME,
    ORDINAL_POSITION,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'YourTableName';

'YourTableName' را با نام واقعی جدول خود جایگزین کنید.

برای شناسایی تمام ستون‌هایی که در پایگاه داده شما از یک نوع داده خاص مانند NVARCHAR استفاده می‌کنند، می‌توانید DATA_TYPE را کوئری کنید:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    DATA_TYPE = 'nvarchar';

کشف اینکه کدام ستون‌ها مقادیر NULL را مجاز می‌دانند برای یکپارچگی داده‌ها و توسعه برنامه حیاتی است. برای این منظور از IS_NULLABLE استفاده کنید:

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

برای یافتن تمام ستون‌هایی که یک مقدار پیش‌فرض تعریف‌شده دارند، فیلتر را بر اساس COLUMN_DEFAULT که NULL نباشد، اعمال کنید:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_DEFAULT
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_DEFAULT IS NOT NULL;

اگر نیاز به یافتن ستون‌هایی دارید که نام آن‌ها از یک الگوی خاص پیروی می‌کند، می‌توانید از عملگر LIKE با COLUMN_NAME استفاده کنید:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME LIKE '%ID%';

این کوئری تمام ستون‌هایی را که شامل ‘ID’ در هر نقطه‌ای از نام خود هستند، برمی‌گرداند.

در حالی که خود INFORMATION_SCHEMA.COLUMNS مستقیماً کلیدهای اصلی (Primary Keys) را نشان نمی‌دهد، می‌توان آن را با INFORMATION_SCHEMA.KEY_COLUMN_USAGE پیوند (join) داد تا این اطلاعات به دست آید. این کوئری ترکیبی به شناسایی ستون‌هایی که بخشی از یک محدودیت کلید اصلی هستند، کمک می‌کند:

SELECT
    C.TABLE_SCHEMA,
    C.TABLE_NAME,
    C.COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS C
INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON C.TABLE_SCHEMA = KCU.TABLE_SCHEMA
    AND C.TABLE_NAME = KCU.TABLE_NAME
    AND C.COLUMN_NAME = KCU.COLUMN_NAME
INNER JOIN
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE
    TC.CONSTRAINT_TYPE = 'PRIMARY KEY';

به طور مشابه، برای مکان‌یابی ستون‌هایی که بخشی از یک رابطه کلید خارجی (Foreign Key) هستند، می‌توانید INFORMATION_SCHEMA.COLUMNS را با INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS و INFORMATION_SCHEMA.KEY_COLUMN_USAGE پیوند دهید. این کوئری پیچیده یک لیست جامع از ستون‌های کلید خارجی و جداول ارجاع‌شده متناظر آن‌ها را فراهم می‌کند:

SELECT
    FK_C.TABLE_SCHEMA AS ForeignKeySchema,
    FK_C.TABLE_NAME AS ForeignKeyTable,
    FK_C.COLUMN_NAME AS ForeignKeyColumn,
    PK_C.TABLE_SCHEMA AS PrimaryKeySchema,
    PK_C.TABLE_NAME AS PrimaryKeyTable,
    PK_C.COLUMN_NAME AS PrimaryKeyColumn
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_KCU ON RC.CONSTRAINT_NAME = FK_KCU.CONSTRAINT_NAME
JOIN
    INFORMATION_SCHEMA.COLUMNS FK_C ON FK_KCU.TABLE_SCHEMA = FK_C.TABLE_SCHEMA
    AND FK_KCU.TABLE_NAME = FK_C.TABLE_NAME
    AND FK_KCU.COLUMN_NAME = FK_C.COLUMN_NAME
JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_KCU ON RC.UNIQUE_CONSTRAINT_NAME = PK_KCU.CONSTRAINT_NAME
JOIN
    INFORMATION_SCHEMA.COLUMNS PK_C ON PK_KCU.TABLE_SCHEMA = PK_C.TABLE_SCHEMA
    AND PK_KCU.TABLE_NAME = PK_C.TABLE_NAME
    AND PK_KCU.COLUMN_NAME = PK_C.COLUMN_NAME
WHERE
    FK_KCU.ORDINAL_POSITION = PK_KCU.ORDINAL_POSITION;