کشف ساختار SQL Server با INFORMATION_SCHEMA جداول ویوها و ستونها

کشف ساختار پایگاه داده SQL Server با INFORMATION_SCHEMA: جداول، ویوها و ستون‌ها

در دنیای مدیریت پایگاه داده، درک ساختار و فراداده (Metadata) یک پایگاه داده از اهمیت بالایی برخوردار است. `INFORMATION_SCHEMA` در SQL Server مجموعه‌ای از ویوها را فراهم می‌کند که دسترسی استاندارد و سیستماتیک به فراداده پایگاه داده را ممکن می‌سازد. با استفاده از این ویوها، توسعه‌دهندگان و مدیران پایگاه داده می‌توانند به راحتی اطلاعاتی درباره جداول، ویوها، ستون‌ها، روتین‌ها (توابع و رویه‌ها) و محدودیت‌ها به دست آورند. این استاندارد به کاربران اجازه می‌دهد تا پرس‌وجوهایی یکسان را برای پایگاه‌های داده مختلف اجرا کنند، که این یک مزیت بزرگ در زمینه سازگاری و قابلیت حمل کد است.

**INFORMATION_SCHEMA.TABLES: نمایش اطلاعات جداول پایگاه داده**

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

(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE)

* `TABLE_CATALOG`: نام پایگاه داده‌ای که جدول در آن قرار دارد.
* `TABLE_SCHEMA`: نام شمای (Schema) جدول.
* `TABLE_NAME`: نام جدول.
* `TABLE_TYPE`: نوع شیء، که می‌تواند ‘BASE TABLE’ (جدول پایه)، ‘VIEW’ (ویو) یا ‘SYSTEM_TABLE’ (جدول سیستمی) باشد.

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

SELECT *
FROM INFORMATION_SCHEMA.TABLES;

اگر به دنبال تنها جداول پایه (غیر سیستمی و غیر ویو) هستید، می‌توانید شرط `WHERE` را اضافه کنید:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

برای یافتن جداول در یک شمای خاص، مثلاً شمای ‘dbo’:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo';

این ویو برای تحلیل ساختار پایگاه داده و یافتن سریع اشیاء خاص بسیار کارآمد است.

**INFORMATION_SCHEMA.VIEWS: جستجوی جزئیات ویوها**

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

(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE)

* `TABLE_CATALOG`: نام پایگاه داده‌ای که ویو در آن قرار دارد.
* `TABLE_SCHEMA`: نام شمای ویو.
* `TABLE_NAME`: نام ویو.
* `VIEW_DEFINITION`: تعریف کامل SQL ویو، یعنی همان دستور `SELECT` که ویو را تشکیل می‌دهد.
* `CHECK_OPTION`: وضعیت `WITH CHECK OPTION` برای ویو.
* `IS_UPDATABLE`: نشان می‌دهد که آیا ویو قابل به‌روزرسانی است (YES/NO).

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

SELECT *
FROM INFORMATION_SCHEMA.VIEWS;

برای مشاهده تعریف SQL یک ویو خاص، مثلاً ویوی با نام ‘vwAdventureWorksSalesOrder’:

SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'vwAdventureWorksSalesOrder';

این ابزار برای درک منطق پشت ویوها و عیب‌یابی آن‌ها حیاتی است.

**INFORMATION_SCHEMA.COLUMNS: نمایش جزئیات ستون‌های جداول و ویوها**

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

(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME)

* `TABLE_CATALOG`: نام پایگاه داده.
* `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_SCALE`: مقیاس (تعداد ارقام بعد از اعشار) برای ستون‌های عددی.
* `DATETIME_PRECISION`: دقت برای ستون‌های تاریخ و زمان.

برای مشاهده تمامی ستون‌های یک جدول خاص، مثلاً جدول ‘Person.Address’ در پایگاه داده ‘AdventureWorks’:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Address' AND TABLE_SCHEMA = 'Person';

برای یافتن تمام ستون‌هایی که نام آن‌ها شامل ‘ID’ است:

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

این ویو برای مهندسی معکوس پایگاه داده، بررسی تطابق نوع داده‌ها و مستندسازی ساختار داده‌ها بی‌نظیر است.

**سایر ویوهای کلیدی INFORMATION_SCHEMA:**

`INFORMATION_SCHEMA` شامل ویوهای دیگری نیز هست که هر یک به بخش خاصی از فراداده پایگاه داده می‌پردازند:

* **INFORMATION_SCHEMA.ROUTINES**: اطلاعاتی درباره توابع، رویه‌های ذخیره‌شده و رویه‌های گسترش‌یافته فراهم می‌کند.
* **INFORMATION_SCHEMA.PARAMETERS**: جزئیات پارامترهای توابع و رویه‌های ذخیره‌شده را نشان می‌دهد.
* **INFORMATION_SCHEMA.KEY_COLUMN_USAGE**: اطلاعاتی درباره ستون‌هایی که بخشی از کلیدهای اصلی (Primary Keys) یا خارجی (Foreign Keys) هستند، ارائه می‌دهد.
* **INFORMATION_SCHEMA.TABLE_CONSTRAINTS**: به شما امکان می‌دهد اطلاعات مربوط به تمام محدودیت‌های جدول (مانند PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) را مشاهده کنید.

با استفاده هوشمندانه از `INFORMATION_SCHEMA`، می‌توانید به سرعت و به طور موثر به فراداده پایگاه داده خود دسترسی پیدا کرده، آن را تحلیل کنید و مدیریت پایگاه داده را بهبود بخشید. این رویکرد استاندارد، کارایی و قابلیت نگهداری کوئری‌های فراداده شما را افزایش می‌دهد.