azure sql database PAAS instance - system tables inaccessible

Question

I spined up Azure SQL Database which is PAAS instance only.

I tried accessing System tables, but to my surprise they were not available.

Please suggest-

1. Please suggest a way to access system tables in Azure SQL Database(PAAS)
2. If accessibility is not permitted, Please suggest rationale for this.
3. Are system tables accessible under Azure SQL Managed Instance.

Answer 1

Hi @Priyank Agarwal ,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.
As I understand, you want to view system tables of Azure SQL Database and could not view.

Try to access Azure SQL Database using SQL Server Management Studio(SSMS) and login to the Database and you would be able to see both System Databases and contents within them as shown below:

Please let us know if you have already tried this and could not view, we suggest you to share screenshot so that we can look into the issue.

For Azure SQL Managed Instance:

Please let us know if my you are facing issue in above steps.

Or share your inputs if you have some other problem in viewing tables or expectation is different to what I understood from question verbatim.

Thank you.

Answer 2

Azure SQL Database (PaaS) is a fully managed platform as a service that provides many benefits like automatic scaling, patching, and backups. However, it does come with some limitations compared to traditional SQL Server installations, including restrictions on accessing system tables.

1. Accessing system tables in Azure SQL Database:

In Azure SQL Database, you do not have access to the full set of system tables available in traditional SQL Server installations. Instead, you have access to a limited set of catalog views and dynamic management views (DMVs) that provide information about your database objects and performance.

To access system information, you can query the available catalog views and DMVs. For example:

SELECT * FROM sys.objects;
SELECT * FROM sys.dm_exec_requests;

You can find a list of supported catalog views in Azure SQL Database here: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/catalog-views-transact-sql

And a list of supported dynamic management views here: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views

1. Rationale for not permitting access to all system tables:

The main reason for this limitation is that Azure SQL Database is a managed service where the underlying infrastructure, including the server and its resources, is managed by Microsoft. Providing full access to system tables could potentially expose sensitive information or allow actions that could compromise the stability, performance, or security of the service.

1. System tables access in Azure SQL Managed Instance:

Azure SQL Managed Instance is another PaaS offering that provides more features and compatibility with SQL Server compared to Azure SQL Database. It is designed for easy migration of on-premises SQL Server workloads to the cloud.

With Azure SQL Managed Instance, you have more access to system tables and other SQL Server features. However, there are still some differences compared to on-premises SQL Server installations. You can review the differences and limitations here: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-info

In summary, while you can't access all system tables in Azure SQL Database, you can use the supported catalog views and dynamic management views to retrieve the necessary information. If you need more compatibility with SQL Server, consider using Azure SQL Managed Instance, which provides greater access to system tables and other SQL Server features.