اسکریپت لیست Schema های ایجاد شده توسط کاربر در یک دیتابیس

اسکریپت لیست Schemaهای ایجادشده توسط کاربر در یک دیتابیس

در سیستم‌های مدیریت پایگاه داده، Schema یکی از مفاهیم مهم برای سازمان‌دهی و مدیریت اشیای دیتابیس است.
یک Schema معمولاً مجموعه‌ای از اشیای مرتبط مانند جدول‌ها (Tables)، نماها (Views)، رویه‌های ذخیره‌شده (Stored Procedures)، توابع (Functions) و سایر اجزای پایگاه داده را در خود جای می‌دهد.
استفاده از Schemaها به مدیران پایگاه داده کمک می‌کند تا ساختار دیتابیس را به شکل منظم‌تری طراحی کنند و دسترسی کاربران را به اشیای مختلف کنترل کنند.
در بسیاری از مواقع، مدیر پایگاه داده نیاز دارد فهرستی از Schemaهایی تهیه کند که توسط کاربران ایجاد شده‌اند.
این کار می‌تواند برای بررسی ساختار دیتابیس، مستندسازی، عیب‌یابی، ارزیابی امنیتی و شناسایی Schemaهای غیرضروری انجام شود.
برای انجام این کار، می‌توان از اسکریپت‌های SQL استفاده کرد.
البته روش نوشتن اسکریپت به نوع سیستم مدیریت پایگاه داده بستگی دارد؛ برای مثال، SQL Server، Oracle، PostgreSQL و MySQL در نحوه مدیریت Schemaها تفاوت‌هایی دارند.
در این مطلب، تمرکز اصلی بر Microsoft SQL Server است.

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

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

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

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

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

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

مفهوم Schema در SQL Server

در SQL Server، Schema یک فضای نام (Namespace) برای سازمان‌دهی اشیای دیتابیس است.
هر شیء مانند جدول یا View می‌تواند به یک Schema مشخص تعلق داشته باشد.
برای مثال، فرض کنید دیتابیسی به نام CompanyDB داریم.
در این دیتابیس ممکن است Schemaهای زیر وجود داشته باشند:

• dbo

  Schema پیش‌فرض و رایج در بسیاری از دیتابیس‌ها.

• Sales

  برای نگهداری اشیای مربوط به فروش.

• HumanResources

  برای اطلاعات منابع انسانی.

• Accounting

  برای اطلاعات حسابداری.

• Reporting

  برای گزارش‌ها و نماهای تحلیلی.

استفاده از Schema باعث می‌شود اشیای بخش‌های مختلف سیستم از یکدیگر تفکیک شوند.
برای نمونه، دو Schema مختلف می‌توانند جدول‌هایی با نام یکسان داشته باشند؛ مانند Sales.Customers و Support.Customers.
همچنین، Schemaها در مدیریت مجوزها نقش مهمی دارند.
مدیر دیتابیس می‌تواند مجوزهای دسترسی به یک Schema را برای کاربران یا نقش‌های مشخص تعریف کند.

اسکریپت ساده برای نمایش Schemaها

در SQL Server، اطلاعات مربوط به Schemaهای دیتابیس از طریق نماهای سیستمی و توابع اطلاعاتی قابل دریافت است.
یکی از روش‌های ساده، استفاده از نمای سیستمی sys.schemas است.

SELECT
    name AS SchemaName,
    schema_id AS SchemaID,
    principal_id AS OwnerPrincipalID
FROM sys.schemas
ORDER BY name;

این اسکریپت فهرست Schemaهای قابل مشاهده در دیتابیس فعلی را نمایش می‌دهد.
ستون SchemaName نام Schema را مشخص می‌کند.
ستون SchemaID شناسه داخلی آن را نشان می‌دهد و ستون OwnerPrincipalID شناسه مالک Schema را نگهداری می‌کند.
برای اجرای این اسکریپت، ابتدا باید به دیتابیس موردنظر متصل شوید.
برای مثال:

USE CompanyDB;
GO
SELECT
    name AS SchemaName,
    schema_id AS SchemaID,
    principal_id AS OwnerPrincipalID
FROM sys.schemas
ORDER BY name;

با اجرای این دستور، اطلاعات Schemaهای دیتابیس CompanyDB نمایش داده می‌شود.

اسکریپت برای نمایش Schemaهای ایجادشده توسط کاربران

اگر هدف، شناسایی Schemaهای سفارشی ایجادشده برای کاربردهای سازمانی باشد، معمولاً لازم است Schemaهای سیستمی و Schema پیش‌فرض dbo از نتایج حذف شوند.
نمونه اسکریپت زیر این کار را انجام می‌دهد:

USE CompanyDB;
GO
SELECT
    s.name AS SchemaName,
    s.schema_id AS SchemaID,
    USER_NAME(s.principal_id) AS SchemaOwner
FROM sys.schemas AS s
WHERE s.name NOT IN (
    'dbo',
    'guest',
    'INFORMATION_SCHEMA',
    'sys'
)
ORDER BY s.name;

در این اسکریپت، از sys.schemas برای دریافت اطلاعات Schemaها استفاده شده است.
شرط WHERE نام‌های مشخص‌شده را از خروجی حذف می‌کند و تابع ()USER_NAME نام مالک Schema را نمایش می‌دهد.
نکته مهم: حذف این نام‌ها صرفاً یک فیلتر مبتنی بر نام است و تضمین نمی‌کند که تمام نتایج باقی‌مانده واقعاً توسط کاربران ایجاد شده باشند. همچنین ممکن است Schemaهای سفارشی با مالکیت متفاوت یا Schemaهای سیستمی دیگری وجود داشته باشند.
بنابراین، اگر هدف تشخیص دقیق منشأ ایجاد Schema باشد، باید اطلاعات بیشتری بررسی شود.

نمایش Schema همراه با اطلاعات مالک

در بعضی سازمان‌ها، صرفاً دانستن نام Schema کافی نیست.
مدیر دیتابیس ممکن است بخواهد مالک هر Schema را نیز بررسی کند تا مشخص شود کدام کاربر یا نقش مالک آن است.
اسکریپت زیر اطلاعات بیشتری ارائه می‌دهد:

SELECT
    s.name AS SchemaName,
    s.schema_id AS SchemaID,
    dp.name AS OwnerName,
    dp.type_desc AS OwnerType
FROM sys.schemas AS s
LEFT JOIN sys.database_principals AS dp
    ON s.principal_id = dp.principal_id
WHERE s.name <> 'dbo'
ORDER BY s.name;

در این مثال، نمای sys.database_principals به sys.schemas متصل شده است.
این اتصال امکان نمایش نام و نوع مالک را فراهم می‌کند.
ستون OwnerName نام مالک Schema را نشان می‌دهد و OwnerType مشخص می‌کند که مالک از چه نوعی است؛ برای مثال، یک کاربر دیتابیس یا یک نقش دیتابیس.
این اطلاعات برای بررسی مجوزها، شناسایی مالکیت‌های غیرمنتظره و مستندسازی ساختار پایگاه داده مفید هستند.

بررسی اشیای موجود در هر Schema

پس از شناسایی Schemaها، ممکن است لازم باشد بدانیم هر Schema چه اشیایی را در خود نگهداری می‌کند.
اسکریپت زیر نام Schema، نام شیء و نوع آن را نمایش می‌دهد:

SELECT
    s.name AS SchemaName,
    o.name AS ObjectName,
    o.type_desc AS ObjectType,
    o.create_date AS CreateDate,
    o.modify_date AS ModifyDate
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
    ON o.schema_id = s.schema_id
WHERE s.name <> 'dbo'
ORDER BY
    s.name,
    o.type_desc,
    o.name;

این اسکریپت اطلاعات اشیایی را نشان می‌دهد که در نمای sys.objects ثبت شده‌اند؛ از جمله بسیاری از جدول‌ها، Viewها، رویه‌های ذخیره‌شده و توابع.
ستون CreateDate تاریخ ایجاد شیء و ستون ModifyDate آخرین تاریخ تغییر ثبت‌شده برای آن را نمایش می‌دهد.
این تاریخ‌ها مربوط به خود شیء هستند، نه الزاماً زمان ایجاد Schema.
توجه کنید که این کوئری به‌تنهایی فهرست Schemaهای خالی را نشان نمی‌دهد؛ زیرا فقط Schemaهایی در خروجی ظاهر می‌شوند که شیء قابل مشاهده‌ای در sys.objects داشته باشند.

 نکات امنیتی و مدیریتی

هنگام تهیه فهرست Schemaهای دیتابیس، رعایت چند نکته ضروری است:

1. مجوزهای دسترسی

نتایج قابل مشاهده ممکن است تحت تأثیر مجوزهای کاربر اجراکننده اسکریپت قرار بگیرند.
بنابراین، خروجی یک کاربر عادی ممکن است با خروجی مدیر دیتابیس متفاوت باشد.

2. مالکیت Schema

مالک Schema لزوماً همان کاربری نیست که آن را ایجاد کرده است.
مالکیت می‌تواند تغییر کند و اطلاعات مالک فعلی، به‌تنهایی سابقه ایجاد را اثبات نمی‌کند.

3. Schemaهای سیستمی

حذف Schemaهایی مانند sys و INFORMATION_SCHEMA برای تهیه گزارش Schemaهای سفارشی رایج است، اما باید متناسب با نیاز سازمان انجام شود.

4. مستندسازی

بهتر است نام Schema، مالک، اشیای موجود و کاربرد تجاری آن ثبت شوند تا مدیریت دیتابیس در آینده ساده‌تر باشد.

5. پرهیز از حذف خودکار

شناسایی Schemaهای بدون استفاده به معنی مجاز بودن حذف آن‌ها نیست.
پیش از هرگونه حذف، باید وابستگی‌ها، مجوزها و نیازمندی‌های برنامه بررسی شوند.

نتیجه‌گیری

تهیه فهرست Schemaهای ایجادشده توسط کاربران یکی از فعالیت‌های مهم در مدیریت و نگهداری پایگاه داده است.
در Microsoft SQL Server، نمای سیستمی sys.schemas ابزار اصلی برای دریافت اطلاعات Schemaها محسوب می‌شود.
با ترکیب این نما با sys.database_principals و sys.objects می‌توان گزارش‌هایی شامل نام Schema، مالک، اشیای موجود و اطلاعات مدیریتی تهیه کرد.
با این حال، باید میان «Schema سفارشی»، «Schema با مالک فعلی مشخص» و «Schema ایجادشده توسط یک کاربر مشخص» تفاوت قائل شد. اطلاعات کاتالوگ دیتابیس معمولاً وضعیت فعلی را نشان می‌دهند و برای اثبات اینکه دقیقاً چه کسی Schema را ایجاد کرده است، ممکن است به لاگ‌های ممیزی (Audit)، رویدادهای ثبت‌شده یا سابقه تغییرات نیاز باشد.