Namespace LinqToDB.Tools.DataProvider.SqlServer.Schemas

Classes

AvailabilitySchema

AvailabilitySchema.AvailabilityDatabasesCluster

sys.availability_databases_cluster (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each availability database on the instance of SQL Server that is hosting an availability replica for any Always On availability group in the Windows Server Failover Clustering (WSFC) cluster, regardless of whether the local copy database has been joined to the availability group yet.

note

When a database is added to an availability group, the primary database is automatically joined to the group. Secondary databases must be prepared on each secondary replica before they can be joined to the availability group.

See sys.availability_databases_cluster.

AvailabilitySchema.AvailabilityGroup

sys.availability_groups (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each availability group for which the local instance of SQL Server hosts an availability replica. Each row contains a cached copy of the availability group metadata.

See sys.availability_groups.

AvailabilitySchema.AvailabilityGroupListener

sys.availability_group_listeners (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

For each Always On availability group, returns either zero rows indicating that no network name is associated with the availability group, or returns a row for each availability-group listener configuration in the Windows Server Failover Clustering (WSFC) cluster. This view displays the real-time configuration gathered from cluster.

note

This catalog view does not describe details of an IP configuration, that was defined in the WSFC cluster.

See sys.availability_group_listeners.

AvailabilitySchema.AvailabilityGroupListenerIpAddress

sys.availability_group_listener_ip_addresses (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for every IP address that is associated with any Always On availability group listener in the Windows Server Failover Clustering (WSFC) cluster.
Primary key: listener_id + ip_address + ip_sub_mask

See sys.availability_group_listener_ip_addresses.

AvailabilitySchema.AvailabilityGroupsCluster

sys.availability_groups_cluster (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each Always On availability group in the Windows Server Failover Clustering (WSFC) . Each row contains the availability group metadata from the WSFC cluster.

See sys.availability_groups_cluster.

AvailabilitySchema.AvailabilityReadOnlyRoutingList

sys.availability_read_only_routing_lists (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for the read only routing list of each availability replica in an Always On availability group in the WSFC failover cluster.

See sys.availability_read_only_routing_lists.

AvailabilitySchema.AvailabilityReplica

sys.availability_replicas (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each of the availability replicas that belong to any Always On availability group in the WSFC failover cluster.
If the local server instance is unable to talk to the WSFC failover cluster, for example because the cluster is down or quorum has been lost, only rows for local availability replicas are returned. These rows will contain only the columns of data that are cached locally in metadata.

See sys.availability_replicas.

AvailabilitySchema.DataContext

AzureSQLDatabaseSchema

AzureSQLDatabaseSchema.BandwidthUsage

sys.bandwidth_usage (Azure SQL Database)

Applies to: √ Azure SQL Database √ Azure SQL Managed Instance

note

This applies only to Azure SQL DatabaseV11.

Returns information about the network bandwidth used by each database in a Azure SQL Database V11 database server, . Each row returned for a given database summarizes a single direction and class of usage over a one-hour period.
This has been deprecated in a Azure SQL Database.
The sys.bandwidth_usage view contains the following columns.

See sys.bandwidth_usage.

AzureSQLDatabaseSchema.DataContext

AzureSQLDatabaseSchema.DatabaseConnectionStat

sys.database_connection_stats (Azure SQL Database)

Applies to: √ Azure SQL Database

Contains statistics for SQL Database database connectivity events, providing an overview of database connection successes and failures. For more information about connectivity events, see Event Types in sys.event_log (Azure SQL Database).

See sys.database_connection_stats.

AzureSQLDatabaseSchema.DatabaseFirewallRule

sys.database_firewall_rules (Azure SQL Database)

Applies to: √ Azure SQL Database √ Azure SQL Managed Instance

Returns information about the database-level firewall settings associated with your Microsoft Azure SQL Database. Database-level firewall settings are particularly useful when using contained database users. For more information, see Contained Database Users - Making Your Database Portable.
The sys.database_firewall_rules view contains the following columns:

See sys.database_firewall_rules.

AzureSQLDatabaseSchema.DatabaseServiceObjective

sys.database_service_objectives (Azure SQL Database)

Applies to: √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Returns the edition (service tier), service objective (pricing tier) and elastic pool name, if any, for an Azure SQL database or an Azure Synapse Analytics. If logged on to the master database in an Azure SQL Database server, returns information on all databases. For Azure Synapse Analytics, you must be connected to the master database.

For information on pricing, see SQL Database options and performance: SQL Database Pricing and Azure Synapse Analytics Pricing.
To change the service settings, see ALTER DATABASE (Azure SQL Database) and ALTER DATABASE (Azure Synapse Analytics).
The sys.database_service_objectives view contains the following columns.

See sys.database_service_objectives.

AzureSQLDatabaseSchema.DatabaseUsage

sys.database_usage (Azure SQL Database)

Applies to: √ Azure SQL Database √ Azure SQL Managed Instance

Note: This applies only to Azure SQL Database V11.
Lists the number, type, and duration of databases on the SQL Database server.
The sys.database_usage view contains the following columns.

See sys.database_usage.

AzureSQLDatabaseSchema.ElasticPoolResourceStat

sys.elastic_pool_resource_stats (Azure SQL Database)

Applies to: √ Azure SQL Database √ Azure SQL Managed Instance

Returns resource usage statistics for all the elastic pools in a SQL Database server. For each elastic pool, there is one row for each 15 second reporting window (four rows per minute). This includes CPU, IO, Log, storage consumption and concurrent request/session utilization by all databases in the pool. This data is retained for 14 days.
||
|-|
|Applies to: SQL Database V12.|

See sys.elastic_pool_resource_stats.

AzureSQLDatabaseSchema.EventLog

sys.event_log (Azure SQL Database)

Applies to: √ Azure SQL Database

Returns successful Azure SQL Database database connections, connection failures, and deadlocks. You can use this information to track or troubleshoot your database activity with SQL Database.
> [!CAUTION]
> For installations having a large number of databases or high numbers of logins, activity in sys.event_log can cause limitations in performance, high CPU usage, and possibly result in login failures. Queries of sys.event_log can contribute to the problem. Microsoft is working to resolve this issue. In the meantime, to reduce the impact of this issue, limit queries of sys.event_log. Users of the NewRelic SQL Server plugin should visit Microsoft Azure SQL Database plugin tuning & performance tweaks for additional configuration information.
The sys.event_log view contains the following columns.

See sys.event_log.

AzureSQLDatabaseSchema.FirewallRule

sys.firewall_rules (Azure SQL Database)

Applies to: √ Azure SQL Database √ Azure SQL Managed Instance

Returns information about the server-level firewall settings associated with your Microsoft Azure SQL Database.
The sys.firewall_rules view contains the following columns:

See sys.firewall_rules.

AzureSQLDatabaseSchema.ResourceStat

sys.resource_stats (Azure SQL Database)

Applies to: √ Azure SQL Database

Returns CPU usage and storage data for an Azure SQL Database. The data is collected and aggregated within five-minute intervals. For each user database, there is one row for every five-minute reporting window in which there is a change in resource consumption. The data returned includes CPU usage, storage size change, and database SKU modification. Idle databases with no changes may not have rows for every five-minute interval. Historical data is retained for approximately 14 days.
The sys.resource_stats view has different definitions depending on the version of the Azure SQL Database Server that the database is associated with. Consider these differences and any modifications your application requires when upgrading to a new server version.

note

This dynamic management view applies to Azure SQL Database only. For an equivalent view for Azure SQL Managed Instance, use sys.server_resource_stats.

The following table describes the columns available in a v12 server:

See sys.resource_stats.

AzureSQLDatabaseSchema.ResourceUsage

sys.resource_usage (Azure SQL Database)

Applies to: √ Azure SQL Database √ Azure SQL Managed Instance

important

This feature is in a preview state. Do not take a dependency on the specific implementation of this feature because the feature might be changed or removed in a future release.
While in a preview state, the Azure SQL Database operations team might turn data collection off and on for this DMV:
- When turned on, the DMV returns current data as it is aggregated.
- When turned off, the DMV returns historical data, which might be stale.

Provides hourly summary of resource usage data for user databases in the current server. Historical data is retained for 90 days.
For each user database, there is one row for every hour in continuous fashion. Even if the database was idle during that hour, there is one row, and the usage_in_seconds value for that database will be 0. Storage usage and SKU information is rolled up for the hour appropriately.

See sys.resource_usage.

AzureSQLDatabaseSchema.ServerResourceStat

sys.server_resource_stats (Azure SQL Managed Instance)

√ Azure SQL Managed Instance
Returns CPU usage, IO, and storage data for Azure SQL Managed Instance. The data is collected, aggregated and updated within 5 to 10 minutes intervals. There is one row for every 15 seconds reporting. The data returned includes CPU usage, storage size, IO utilization, and SKU. Historical data is retained for approximately 14 days.
The sys.server_resource_stats view has different definitions depending on the version of the Azure SQL Managed Instance that the database is associated with. Consider these differences and any modifications your application requires when upgrading to a new server version.

note

This dynamic management view applies to Azure SQL Managed Instance only. For an equivalent view for Azure SQL Database, use sys.resource_stats.

See sys.server_resource_stats.

AzureSynapseAnalyticsSchema

AzureSynapseAnalyticsSchema.ColumnDistributionProperty

sys.pdw_column_distribution_properties (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Holds distribution information for columns.

See sys.pdw_column_distribution_properties.

AzureSynapseAnalyticsSchema.DataContext

AzureSynapseAnalyticsSchema.DatabaseMapping

sys.pdw_database_mappings (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Maps the database_ids of databases to the physical name used on Compute nodes, and provides the principal id of the database owner on the system. Join sys.pdw_database_mappings to sys.databases and sys.pdw_nodes_pdw_physical_databases.

See sys.pdw_database_mappings.

AzureSynapseAnalyticsSchema.DiagEvent

sys.pdw_diag_events (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Holds information about events that can be included in diagnostic sessions on the system.

See sys.pdw_diag_events.

AzureSynapseAnalyticsSchema.DiagEventProperty

sys.pdw_diag_event_properties (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Holds information about which properties are associated with diagnostic events.

See sys.pdw_diag_event_properties.

AzureSynapseAnalyticsSchema.DiagSession

sys.pdw_diag_sessions (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Holds information regarding the various diagnostic sessions that have been created on the system.

See sys.pdw_diag_sessions.

AzureSynapseAnalyticsSchema.Distribution

sys.pdw_distributions (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Holds information about the distributions on the appliance. It lists one row per appliance distribution.

See sys.pdw_distributions.

AzureSynapseAnalyticsSchema.HealthAlert

sys.pdw_health_alerts (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Stores properties for the different alerts that can occur on the system; this is a catalog table for alerts.

See sys.pdw_health_alerts.

AzureSynapseAnalyticsSchema.HealthComponent

sys.pdw_health_components (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Stores information about all components and devices that exist in the system. These include hardware, storage devices, and network devices.

See sys.pdw_health_components.

AzureSynapseAnalyticsSchema.HealthComponentGroup

sys.pdw_health_component_groups (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Stores information about logical groupings of components and devices.

See sys.pdw_health_component_groups.

AzureSynapseAnalyticsSchema.HealthComponentProperty

sys.pdw_health_component_properties (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Stores properties that describe a device. Some properties show device status and some properties describe the device itself.

See sys.pdw_health_component_properties.

AzureSynapseAnalyticsSchema.HealthComponentStatusMapping

sys.pdw_health_component_status_mappings (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Defines the mapping between the Microsoft Azure Synapse Analytics component statuses and the manufacturer-defined component names.

See sys.pdw_health_component_status_mappings.

AzureSynapseAnalyticsSchema.IndexMapping

sys.pdw_index_mappings (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Maps the logical indexes to the physical name used on Compute nodes as reflected by a unique combination of object_id of the table holding the index and the index_id of a particular index within that table.

See sys.pdw_index_mappings.

AzureSynapseAnalyticsSchema.LoaderBackupRun

sys.pdw_loader_backup_runs (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains information about ongoing and completed backup and restore operations in Azure Synapse Analytics, and about ongoing and completed backup, restore, and load operations in Analytics Platform System (PDW). The information persists across system restarts.

See sys.pdw_loader_backup_runs.

AzureSynapseAnalyticsSchema.LoaderBackupRunDetail

sys.pdw_loader_backup_run_details (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains further detailed information, beyond the information in sys.pdw_loader_backup_runs (Transact-SQL), about ongoing and completed backup and restore operations in Azure Synapse Analytics and about ongoing and completed backup, restore, and load operations in Analytics Platform System (PDW). The information persists across system restarts.

See sys.pdw_loader_backup_run_details.

AzureSynapseAnalyticsSchema.LoaderRunStage

sys.pdw_loader_run_stages (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Contains information about ongoing and completed load operations in Analytics Platform System (PDW). The information persists across system restarts.

See sys.pdw_loader_run_stages.

AzureSynapseAnalyticsSchema.MaterializedViewColumnDistributionProperty

sys.pdw_materialized_view_column_distribution_properties (Transact-SQL)

Applies to: √ Azure Synapse Analytics

Displays distribution information for columns in a materialized view.

See sys.pdw_materialized_view_column_distribution_properties.

AzureSynapseAnalyticsSchema.MaterializedViewDistributionProperty

sys.pdw_materialized_view_distribution_properties (Transact-SQL) (preview)

Applies to: √ Azure Synapse Analytics

Displays distribution information materialized views.

See sys.pdw_materialized_view_distribution_properties.

AzureSynapseAnalyticsSchema.MaterializedViewMapping

sys.pdw_materialized_view_mappings (Transact-SQL)

Applies to: √ Azure Synapse Analytics

Ties the materialized view to internal object names by object_id.
The columns physical_name and object_id form the key for this catalog view.

See sys.pdw_materialized_view_mappings.

AzureSynapseAnalyticsSchema.NodesColumn

sys.pdw_nodes_columns (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Shows columns for user-defined tables and user-defined views.

See sys.pdw_nodes_columns.

AzureSynapseAnalyticsSchema.NodesColumnStoreDictionary

sys.pdw_nodes_column_store_dictionaries (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each dictionary used in columnstore indexes. Dictionaries are used to encode some, but not all data types, therefore not all columns in a columnstore index have dictionaries. A dictionary can exist as a primary dictionary (for all segments) and possibly for other secondary dictionaries used for a subset of the column's segments.

See sys.pdw_nodes_column_store_dictionaries.

AzureSynapseAnalyticsSchema.NodesColumnStoreRowGroup

sys.pdw_nodes_column_store_row_groups (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Provides clustered columnstore index information on a per-segment basis to help the administrator make system management decisions in Azure Synapse Analytics. sys.pdw_nodes_column_store_row_groups has a column for the total number of rows physically stored (including those marked as deleted) and a column for the number of rows marked as deleted. Use sys.pdw_nodes_column_store_row_groups to determine which row groups have a high percentage of deleted rows and should be rebuilt.

See sys.pdw_nodes_column_store_row_groups.

AzureSynapseAnalyticsSchema.NodesColumnStoreSegment

sys.pdw_nodes_column_store_segments (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each column in a columnstore index.

See sys.pdw_nodes_column_store_segments.

AzureSynapseAnalyticsSchema.NodesIndex

sys.pdw_nodes_indexes (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns indexes for Azure Synapse Analytics.

See sys.pdw_nodes_indexes.

AzureSynapseAnalyticsSchema.NodesPartition

sys.pdw_nodes_partitions (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each partition of all the tables, and most types of indexes in a Azure Synapse Analytics database. All tables and indexes contain at least one partition, whether or not they are explicitly partitioned.

See sys.pdw_nodes_partitions.

AzureSynapseAnalyticsSchema.NodesPhysicalDatabase

sys.pdw_nodes_pdw_physical_databases (Transact-SQL)

Applies to: √ Analytics Platform System (PDW)

Contains a row for each physical database on a compute node. Aggregate physical database information to get detailed information about databases. To combine information, join the sys.pdw_nodes_pdw_physical_databases to the sys.pdw_database_mappings and sys.databases tables.

See sys.pdw_nodes_pdw_physical_databases.

AzureSynapseAnalyticsSchema.NodesTable

sys.pdw_nodes_tables (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each table object that a principal either owns or on which the principal has been granted some permission.

See sys.pdw_nodes_tables.

AzureSynapseAnalyticsSchema.PermanentTableMapping

sys.pdw_permanent_table_mappings (Transact-SQL)

Applies to: √ Azure Synapse Analytics

Ties permanent user tables to internal object names by object_id.

note

sys.pdw_permanent_table_mappings holds mappings to permanent tables and does not include temporary or external table mappings.

See sys.pdw_permanent_table_mappings.

AzureSynapseAnalyticsSchema.ReplicatedTableCacheState

sys.pdw_replicated_table_cache_state (Transact-SQL)

Applies to: √ Azure Synapse Analytics

Returns the state of the cache associated with a replicated table by object_id.

See sys.pdw_replicated_table_cache_state.

AzureSynapseAnalyticsSchema.TableDistributionProperty

sys.pdw_table_distribution_properties (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Holds distribution information for tables.

See sys.pdw_table_distribution_properties.

AzureSynapseAnalyticsSchema.TableMapping

sys.pdw_table_mappings (Transact-SQL)

Applies to: √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Ties user tables to internal object names by object_id.

See sys.pdw_table_mappings.

AzureSynapseAnalyticsSchema.WorkloadManagementWorkloadClassifier

sys.workload_management_workload_classifiers (Transact-SQL)

Applies to: √ Azure Synapse Analytics

Returns details for workload classifiers.

See sys.workload-management-workload-classifiers.

AzureSynapseAnalyticsSchema.WorkloadManagementWorkloadClassifierDetail

sys.workload_management_workload_classifier_details (Transact-SQL)

Applies to: √ Azure Synapse Analytics

Returns details for each classifier.

See sys.workload-management-workload-classifier-details.

CLRAssemblySchema

CLRAssemblySchema.Assembly

sys.assemblies (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns a row for each assembly.

See sys.assemblies.

CLRAssemblySchema.AssemblyFile

sys.assembly_files (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each file that makes up an assembly.

See sys.assembly_files.

CLRAssemblySchema.AssemblyReference

sys.assembly_references (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each pair of assemblies where one is directly referencing another.

See sys.assembly_references.

CLRAssemblySchema.DataContext

CLRAssemblySchema.TrustedAssembly

sys.trusted_assemblies (Transact-SQL)

Applies to: √ SQL Server 2017 (14.x) and later

Contains a row for each trusted assembly for the server.
Transact-SQL Syntax Conventions

See sys.trusted_assemblies.

ChangeTrackingSchema

ChangeTrackingSchema.ChangeTrackingDatabase

Change Tracking Catalog Views - sys.change_tracking_databases

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each database that has change tracking enabled.

See sys.change_tracking_databases.

ChangeTrackingSchema.ChangeTrackingTable

Change Tracking Catalog Views - sys.change_tracking_tables

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each table in the current database that has change tracking enabled.

See sys.change_tracking_tables.

ChangeTrackingSchema.DataContext

CompatibilitySchema

CompatibilitySchema.AltFile

sys.sysaltfiles (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Under special circumstances, contains rows corresponding to the files in a database.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysaltfiles.

CompatibilitySchema.CacheObject

sys.syscacheobjects (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about how the cache is used.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.syscacheobjects.

CompatibilitySchema.Charset

sys.syscharsets (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains one row for each character set and sort order defined for use by the SQL Server Database Engine. One of the sort orders is marked in sysconfigures as the default sort order. This is the only one actually being used.

See sys.syscharsets.

CompatibilitySchema.Column

sys.syscolumns (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for every column in every table and view, and a row for each parameter in a stored procedure in the database.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.syscolumns.

CompatibilitySchema.Comment

sys.syscomments (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.

important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use sys.sql_modules instead. For more information, see sys.sql_modules (Transact-SQL).

See sys.syscomments.

CompatibilitySchema.Configure

sys.sysconfigures (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each configuration option set by a user. sysconfigures contains the configuration options that are defined before the most recent startup of SQL Server, plus any dynamic configuration options set since then.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysconfigures.

CompatibilitySchema.Constraint

sys.sysconstraints (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains mappings of constraints to the objects that own the constraints within the database.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysconstraints.

CompatibilitySchema.CurConfig

sys.syscurconfigs (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains an entry for each current configuration option. Also, this view contains four entries that describe the configuration structure. syscurconfigs is built dynamically when queried by a user. For more information, see sys.sysconfigures (Transact-SQL).

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.syscurconfigs.

CompatibilitySchema.DataContext

CompatibilitySchema.Database

sys.sysdatabases (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains one row for each database in an instance of Microsoft SQL Server. When SQL Server is first installed, sysdatabases contains entries for the master, model, msdb, and tempdb databases.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysdatabases.

CompatibilitySchema.Depend

sys.sysdepends (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains dependency information between objects (views, procedures, and triggers) in the database, and the objects (tables, views, and procedures) that are contained in their definition.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysdepends.

CompatibilitySchema.Device

sys.sysdevices (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each disk backup file, tape backup file, and database file.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysdevices.

CompatibilitySchema.ETable

sys.systypes (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each system-supplied and each user-defined data type defined in the database.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.systypes.

CompatibilitySchema.File

sys.sysfiles (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each file in a database.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysfiles.

CompatibilitySchema.FileGroup

sys.sysfilegroups (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each file group in a database. There is at least one entry in this table that is for the primary file group.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysfilegroups.

CompatibilitySchema.ForeignKey

sys.sysforeignkeys (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about the FOREIGN KEY constraints that are in the definitions of tables in the database.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysforeignkeys.

CompatibilitySchema.FullTextCatalog

sys.sysfulltextcatalogs (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Analytics Platform System (PDW)

Contains information about the full-text catalogs.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysfulltextcatalogs.

CompatibilitySchema.Index

sys.sysindexes (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each index and table in the current database. XML indexes are not supported in this view. Partitioned tables and indexes are not fully supported in this view; use the sys.indexes catalog view instead.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysindexes.

CompatibilitySchema.IndexKey

sys.sysindexkeys (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about the keys or columns in an index of the database.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysindexkeys.

CompatibilitySchema.Language

sys.syslanguages (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains one row for each language present in the instance of SQL Server.

See sys.syslanguages.

CompatibilitySchema.LockInfo

sys.syslockinfo (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about all granted, converting, and waiting lock requests.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

important

This feature has changed from earlier versions of SQL Server. For more information, see Breaking Changes to Database Engine Features in SQL Server 2016.

See sys.syslockinfo.

CompatibilitySchema.Login

sys.syslogins (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each login account.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Applies to: SQL Server ( SQL Server 2008 through [current version](/troubleshoot/sql/general/determine-version-edition-update-level)).

See sys.syslogins.

CompatibilitySchema.Member

sys.sysmembers (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each member of a database role.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysmembers.

CompatibilitySchema.Message

sys.sysmessages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each system error or warning that can be returned by the SQL Server Database Engine. The Database Engine displays the error description on the user's screen.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysmessages.

CompatibilitySchema.Object

sys.sysobjects (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysobjects.

CompatibilitySchema.OleDBUser

sys.sysoledbusers (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

important

This SQL Server 2000 (8.x) system table is included in SQL Server as a view for backward compatibility only. We recommend that you use catalog views instead.

Contains one row for each user and password mapping for the specified linked server. sysoledbusers is stored in the master database.

See sys.sysoledbusers.

CompatibilitySchema.PerfInfo

sys.sysperfinfo (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a Microsoft SQL Server Database Engine representation of the internal performance counters that can be displayed through the Windows System Monitor.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysperfinfo.

CompatibilitySchema.Permission

sys.syspermissions (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about permissions granted and denied to users, groups, and roles in the database.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.syspermissions.

CompatibilitySchema.Process

sys.sysprocesses (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about processes that are running on an instance of SQL Server. These processes can be client processes or system processes. To access sysprocesses, you must be in the master database context, or you must use the master.dbo.sysprocesses three-part name.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysprocesses.

CompatibilitySchema.Protect

sys.sysprotects (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about permissions that have been applied to security accounts in the database by using the GRANT and DENY statements.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysprotects.

CompatibilitySchema.Reference

sys.sysreferences (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains mappings of the FOREIGN KEY constraint definitions to the referenced columns within the database.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysreferences.

CompatibilitySchema.RemoteLogin

sys.sysremotelogins (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each remote user that is permitted to call remote stored procedures on an instance of Microsoft SQL Server.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysremotelogins.

CompatibilitySchema.Server

sys.sysservers (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each server that an instance of SQL Server can access as an OLE DB data source.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysservers.

CompatibilitySchema.User

sys.sysusers (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains one row for each Microsoft Windows user, Windows group, Microsoft SQL Server user, or SQL Server role in the database.

important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See sys.sysusers.

DataCollectorSchema

DataCollectorSchema.CollectionItem

syscollector_collection_items (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns information about an item in a collection set.

See dbo.syscollector_collection_items.

DataCollectorSchema.CollectionSet

syscollector_collection_sets (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Provides information about a collection set, including schedule, collection mode, and its state.

See dbo.syscollector_collection_sets.

DataCollectorSchema.CollectorType

syscollector_collector_types (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Provides information about a collector type for a collection item.

See dbo.syscollector_collector_types.

DataCollectorSchema.ConfigStore

syscollector_config_store (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns properties that apply to the entire data collector, as opposed to a collection set instance. Each row in this view describes a specific data collector property, such as the name of the management data warehouse, and the instance name where the management data warehouse is located.

See dbo.syscollector_config_store.

DataCollectorSchema.DataContext

DataCollectorSchema.ExecutionLog

syscollector_execution_log (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Provides information from the execution log for a collection set or package.

See dbo.syscollector_execution_log.

DataCollectorSchema.ExecutionLogFull

syscollector_execution_log_full (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Provides information about a collection set or package when the execution log is full.

See dbo.syscollector_execution_log_full.

DataCollectorSchema.ExecutionStat

syscollector_execution_stats (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Provides information about task execution for a collection set or package.

See dbo.syscollector_execution_stats.

DataSpacesSchema

DataSpacesSchema.DataContext

DataSpacesSchema.DataSpace

sys.data_spaces (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each data space. This can be a filegroup, partition scheme, or FILESTREAM data filegroup.

See sys.data_spaces.

DataSpacesSchema.DestinationDataSpace

sys.destination_data_spaces (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each data space destination of a partition scheme.

See sys.destination_data_spaces.

DataSpacesSchema.FileGroup

sys.filegroups (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each data space that is a filegroup.

See sys.filegroups.

DataSpacesSchema.PartitionScheme

sys.partition_schemes (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each Data Space that is a partition scheme, with type = PS.

See sys.partition_schemes.

DataTierApplicationsSchema

DataTierApplicationsSchema.DataContext

DataTierApplicationsSchema.Instance

Data-tier Application Views - dbo.sysdac_instances

Applies to: √ SQL Server (all supported versions)

Displays one row for each data-tier application (DAC) instance deployed to an instance of the Database Engine. sysdac_instances belongs to the dbo schema in the msdb database. The following table describes the columns in the sysdac_instances view.

See dbo.sysdac_instances.

DatabaseMailSchema

DatabaseMailSchema.AllItem

sysmail_allitems (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance

Contains one row for each message processed by Database Mail. Use this view when you want to see the status of all messages.
To see only messages with the failed status, use sysmail_faileditems (Transact-SQL). To see only unsent messages, use sysmail_unsentitems (Transact-SQL). To see only messages that were sent, use sysmail_sentitems (Transact-SQL).

See dbo.sysmail_allitems.

DatabaseMailSchema.DataContext

DatabaseMailSchema.EventLog

sysmail_event_log (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each Windows or SQL Server message returned by the Database Mail system. (Message in this context refers to a message such as an error message, not an e-mail message.) Configure the Logging Level parameter by using the Configure System Parameters dialog box of the Database Mail Configuration Wizard, or the sysmail_configure_sp stored procedure, to determine which messages are returned.

See dbo.sysmail_event_log.

DatabaseMailSchema.FailedItem

sysmail_faileditems (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each Database Mail message with the failed status. Use this view to determine which messages were not successfully sent.
To see all messages processed by Database Mail, use sysmail_allitems (Transact-SQL). To see only unsent messages, use sysmail_unsentitems (Transact-SQL). To see only messages that were sent, use sysmail_sentitems (Transact-SQL). To view e-mail attachments, use sysmail_mailattachments (Transact-SQL).

See dbo.sysmail_faileditems.

DatabaseMailSchema.MailAttachment

sysmail_mailattachments (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each attachment submitted to Database Mail. Use this view when you want information about Database Mail attachments. To review all e-mails processed by Database Mail use sysmail_allitems (Transact-SQL).

See dbo.sysmail_mailattachments.

DatabaseMailSchema.SentItem

sysmail_sentitems (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance

Contains one row for each message sent by Database Mail. Use sysmail_sentitems when you want to see which messages were successfully sent.
To see all messages processed by Database Mail, use sysmail_allitems (Transact-SQL). To see only messages with the failed status, use sysmail_faileditems (Transact-SQL). To see only unsent or retrying messages, use sysmail_unsentitems (Transact-SQL). To see e-mail attachments, use sysmail_mailattachments (Transact-SQL).

See dbo.sysmail_sentitems.

DatabaseMailSchema.UnsentItem

sysmail_unsentitems (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance

Contains one row for each Database Mail message with the unsent or retrying status. Messages with unsent or retrying status are still in the mail queue and may be sent at any time. Messages can have the unsent status for the following reasons:
- The message is new, and though the message has been placed on the mail queue, Database Mail is working on other messages and has not yet reached this message.
- The Database Mail external program is not running and no mail is being sent.
Messages can have the retrying status for the following reasons:
- Database Mail has attempted to send the mail, but the SMTP mail server could not be contacted. Database Mail will continue to attempt to send the message using other Database Mail accounts assigned to the profile that sent the message. If no accounts can send the mail, Database Mail will wait for the length of time configured for the Account Retry Delay parameter and then attempt to send the message again. Database Mail uses the Account Retry Attempts parameter to determine how many times to attempt to send the message. Messages retain retrying status as long as Database Mail is attempting to send the message.
Use this view when you want to see how many messages are waiting to be sent and how long they have been in the mail queue. Normally the number of unsent messages will be low. Conduct a benchmark test during normal operations to determine a reasonable number of messages in the message queue for your operations.
To see all messages processed by Database Mail, use sysmail_allitems (Transact-SQL). To see only messages with the failed status, use sysmail_faileditems (Transact-SQL). To see only messages that were sent, use sysmail_sentitems (Transact-SQL).

See dbo.sysmail_unsentitems.

DatabaseMirroringSchema

DatabaseMirroringSchema.DataContext

DatabaseMirroringSchema.DatabaseMirroringWitness

Database Mirroring Witness Catalog Views - sys.database_mirroring_witnesses

Applies to: √ SQL Server (all supported versions)

Contains a row for every witness role that a server plays in a database mirroring partnership.
In a database mirroring session, automatic failover requires a witness server. Ideally, the witness resides on a separate computer from both the principal and mirror servers. The witness does not serve the database. Instead, it monitors the status of the principal and mirror servers. If the principal server fails, the witness may initiate automatic failover to the mirror server.

See sys.database_mirroring_witnesses.

DatabasesAndFilesSchema

DatabasesAndFilesSchema.BackupDevice

sys.backup_devices (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each backup-device registered by using sp_addumpdevice or created in SQL Server Management Studio.

See sys.backup_devices.

DatabasesAndFilesSchema.DataContext

DatabasesAndFilesSchema.Database

sys.databases (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains one row per database in the instance of SQL Server.
If a database is not ONLINE, or AUTO_CLOSE is set to ON and the database is closed, the values of some columns may be NULL. If a database is OFFLINE, the corresponding row is not visible to low-privileged users. To see the corresponding row if the database is OFFLINE, a user must have at least the ALTER ANY DATABASE server-level permission, or the CREATE DATABASE permission in the master database.

See sys.databases.

DatabasesAndFilesSchema.DatabaseAutomaticTuningMode

sys.database\_automatic\_tuning_mode (Transact-SQL)

Applies to: √ SQL Server 2017 (14.x) and later √ Azure SQL Database √ Azure SQL Managed Instance

Returns the Automatic Tuning mode for this database. Refer to ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) for available options.

See sys.database_automatic_tuning_mode.

DatabasesAndFilesSchema.DatabaseAutomaticTuningOption

sys.database\_automatic\_tuning_options (Transact-SQL)

Applies to: √ SQL Server 2017 (14.x) and later √ Azure SQL Database √ Azure SQL Managed Instance

Returns the Automatic Tuning options for this database.

See sys.database_automatic_tuning_options.

DatabasesAndFilesSchema.DatabaseFile

sys.database_files (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row per file of a database as stored in the database itself. This is a per-database view.

See sys.database_files.

DatabasesAndFilesSchema.DatabaseMirroring

sys.database_mirroring (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each database in the instance of SQL Server. If the database is not ONLINE or database mirroring is not enabled, the values of all columns except database_id will be NULL.
To see the row for a database other than master or tempdb, you must either be the database owner or have at least ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission or CREATE DATABASE permission in the master database. To see non-NULL values on a mirror database, you must be a member of the sysadmin fixed server role.

note

If a database does not participate in mirroring, all columns prefixed with 'mirroring_' are NULL.

See sys.database_mirroring.

DatabasesAndFilesSchema.DatabaseRecoveryStatus

sys.database_recovery_status (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row per database. If the database is not opened, the SQL Server Database Engine tries to start it.
To see the row for a database other than master or tempdb, one of the following must apply:
- Be the owner of the database.
- Have ALTER ANY DATABASE or VIEW ANY DATABASE server-level permissions.
- Have CREATE DATABASE permission in the master database.

See sys.database_recovery_status.

DatabasesAndFilesSchema.DatabaseScopedConfiguration

sys.database_scoped_configurations (Transact-SQL)

APPLIES TO: (Yes) SQL Server 2016 and later (Yes) Azure SQL Database (Yes) Azure Synapse Analytics (No) Parallel Data Warehouse

Contains one row per configuration.

See sys.database_scoped_configurations.

DatabasesAndFilesSchema.MasterFile

sys.master_files (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Analytics Platform System (PDW)

Contains a row per file of a database as stored in the master database. This is a single, system-wide view.

See sys.master_files.

EndpointsSchema

EndpointsSchema.DataContext

EndpointsSchema.DatabaseMirroringEndpoint

sys.database_mirroring_endpoints (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for the database mirroring endpoint of an instance of SQL Server.

note

The database mirroring endpoint supports both sessions between database mirroring partners and with witnesses and sessions between the primary replica of a Always On availability group and its secondary replicas.

See sys.database_mirroring_endpoints.

EndpointsSchema.Endpoint

sys.endpoints (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row per endpoint that is created in the system. There is always exactly one SYSTEM endpoint.

See sys.endpoints.

EndpointsSchema.EndpointWebMethod

sys.endpoint_webmethods (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Contains a row FOR EACH SOAP method defined on a SOAP-enabled HTTP endpoint. The combination of the endpoint_id and namespace columns is unique.

See sys.endpoint_webmethods.

EndpointsSchema.HttpEndpoint

sys.http_endpoints (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Contains a row for each endpoint created in the server that uses the HTTP protocol.

See sys.http_endpoints.

EndpointsSchema.ServiceBrokerEndpoint

sys.service_broker_endpoints (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view contains one row for the Service Broker endpoint. For every row in this view, there is a corresponding row with the same endpoint_id in the sys.tcp_endpoints view that contains the TCP configuration metadata. TCP is the only allowed protocol for Service Broker.

See sys.service_broker_endpoints.

EndpointsSchema.SoapEndpoint

sys.soap_endpoints (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Contains one row for each endpoint in the server that carries a SOAP-type payload. For every row in this view, there is a corresponding row with the same endpoint_id in the sys.http_endpoints catalog view that carries the HTTP configuration metadata.

See sys.soap_endpoints.

EndpointsSchema.TcpEndpoint

sys.tcp_endpoints (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each TCP endpoint that is in the system. The endpoints that are described by sys.tcp_endpoints provide an object to grant and revoke the connection privilege. The information that is displayed regarding ports and IP addresses is not used to configure the protocols and may not match the actual protocol configuration. To view and configure protocols, use SQL Server Configuration Manager.

See sys.tcp_endpoints.

ExtendedEventsSchema

ExtendedEventsSchema.DataContext

ExtendedEventsSchema.DatabaseEventSession

sys.database_event_sessions (Azure SQL Database)

Applies to: √ Azure SQL Database √ Azure SQL Managed Instance

Lists all the event session definitions that exist in the current database, in Azure SQL Database.

note

The similar catalog view named sys.server_event_sessions applies only to MicrosoftSQL Server.

||
|-|
|Applies to: SQL Database, and to any later versions.|

See sys.database_event_sessions.

ExtendedEventsSchema.DatabaseEventSessionAction

sys.database_event_session_actions (Azure SQL Database)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance

Returns a row for each action on each event of an event session.
||
|-|
|Applies to: Azure SQL Database V12 and any later versions.|

See sys.database_event_session_actions.

ExtendedEventsSchema.DatabaseEventSessionEvent

sys.database_event_session_events (Azure SQL Database)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance

Returns a row for each event in an event session.
||
|-|
|Applies to: Azure SQL Database V12 and any later versions.|

See sys.database_event_session_events.

ExtendedEventsSchema.DatabaseEventSessionField

sys.database_event_session_fields (Azure SQL Database)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance

Returns a row for each customizable column that was explicitly set on events and targets.
||
|-|
|Applies to: Azure SQL Database V12 and any later versions.|

See sys.database_event_session_fields.

ExtendedEventsSchema.DatabaseEventSessionTarget

sys.database_event_session_targets (Azure SQL Database)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance

Returns a row for each event target for an event session.
||
|-|
|Applies to: Azure SQL Database V12 and any later versions.|

See sys.database_event_session_targets.

ExtendedEventsSchema.ServerEventSession

sys.server_event_sessions (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Lists all the event session definitions that exist in SQL Server.

See sys.server_event_sessions.

ExtendedEventsSchema.ServerEventSessionAction

sys.server_event_session_actions (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each action on each event of an event session.

See sys.server_event_session_actions.

ExtendedEventsSchema.ServerEventSessionEvent

sys.server_event_session_events (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each event in an event session.

See sys.server_event_session_events.

ExtendedEventsSchema.ServerEventSessionField

sys.server_event_session_fields (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each customizable column that was explicitly set on events and targets.

See sys.server_event_session_fields.

ExtendedEventsSchema.ServerEventSessionTarget

sys.server_event_session_targets (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each event target for an event session.

See sys.server_event_session_targets.

ExternalOperationsSchema

ExternalOperationsSchema.DataContext

ExternalOperationsSchema.ExternalDataSource

sys.external_data_sources (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each external data source in the current database for SQL Server, SQL Database, and Azure Synapse Analytics.
Contains a row for each external data source on the server for Analytics Platform System (PDW).

See sys.external_data_sources.

ExternalOperationsSchema.ExternalFileFormat

sys.external_file_formats (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each external file format in the current database for SQL Server, SQL Database, and Azure Synapse Analytics.
Contains a row for each external file format on the server for Analytics Platform System (PDW).

See sys.external_file_formats.

ExternalOperationsSchema.ExternalTable

sys.external_tables (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each external table in the current database.

See sys.external_tables.

FilestreamAndFileTableSchema

FilestreamAndFileTableSchema.DataContext

FilestreamAndFileTableSchema.DatabaseFilestreamOption

sys.database_filestream_options (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Displays information about the level of non-transactional access to FILESTREAM data in FileTables that is enabled. Contains one row for each database in the SQL Server instance.
For more information about FileTables, see FileTables (SQL Server).

See sys.database_filestream_options.

FilestreamAndFileTableSchema.FileTable

sys.filetables (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each FileTable in SQL Server. For more information about FileTables, see FileTables (SQL Server).

See sys.filetables.

FilestreamAndFileTableSchema.FileTableSystemDefinedObject

sys.filetable_system_defined_objects (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Displays a list of the system-defined objects that are related to FileTables. Contains one row for each system-defined object.
When you create a FileTable, related objects such as constraints and indexes are created at the same time. You cannot alter or drop these objects; they disappear only when the FileTable itself is dropped.
For more information about FileTables, see FileTables (SQL Server).

See sys.filetable_system_defined_objects.

FullTextSearchSchema

FullTextSearchSchema.Catalog

sys.fulltext_catalogs (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each full-text catalog.

note

The following columns will be removed in a future release of SQL Server: data_space_id, file_id, and path. Do not use these columns in new development work, and modify applications that currently use any of these columns as soon as possible.

See sys.fulltext_catalogs.

FullTextSearchSchema.DataContext

FullTextSearchSchema.DocumentType

sys.fulltext_document_types (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns a row for each document type that is available for full-text indexing operations. Each row represents the IFilter interface that is registered in the instance of SQL Server.

See sys.fulltext_document_types.

FullTextSearchSchema.Index

sys.fulltext_indexes (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row per full-text index of a tabular object.

See sys.fulltext_indexes.

FullTextSearchSchema.IndexCatalogUsage

sys.fulltext_index_catalog_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns a row for each full-text catalog to full-text index reference.

See sys.fulltext_index_catalog_usages.

FullTextSearchSchema.IndexColumn

sys.fulltext_index_columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row for each column that is part of a full-text index.

See sys.fulltext_index_columns.

FullTextSearchSchema.IndexFragment

sys.fulltext_index_fragments (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

A fulltext index uses internal tables called *full-text index fragments* to store the inverted index data. This view can be used to query the metadata about these fragments. This view contains a row for each full-text index fragment in every table that contains a full-text index.

See sys.fulltext_index_fragments.

FullTextSearchSchema.Language

sys.fulltext_languages (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

This catalog view contains one row per language whose word breakers are registered with SQL Server. Each row displays the LCID and name of the language. When word breakers are registered for a language, its other linguistic resources-stemmers, noise words (stopwords), and thesaurus files-become available to full-text indexing/querying operations. The value of name or lcid can be specified in the full-text queries and full-text index Transact\-SQL statements.

See sys.fulltext_languages.

FullTextSearchSchema.RegisteredSearchProperty

sys.registered_search_properties (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row for each search property contained by any search property list on the current database.

See sys.registered_search_properties.

FullTextSearchSchema.RegisteredSearchPropertyList

sys.registered_search_property_lists (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each search property list on the current database.

See sys.registered_search_property_lists.

FullTextSearchSchema.SemanticLanguage

sys.fulltext_semantic_languages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each language whose statistics model is registered with the instance of SQL Server. When a language model is registered, that language is enabled for semantic indexing.
This catalog view is similar to sys.fulltext_languages (Transact-SQL).

See sys.fulltext_semantic_languages.

FullTextSearchSchema.SemanticLanguageStatisticsDatabase

sys.fulltext_semantic_language_statistics_database (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row about the semantic language statistics database installed on the current instance of SQL Server.
You can query this view to find out about the semantic language statistics component required for semantic processing.

See sys.fulltext_semantic_language_statistics_database.

FullTextSearchSchema.StopWord

sys.fulltext_stopwords (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row per stopword for all stoplists in the database.

See sys.fulltext_stopwords.

FullTextSearchSchema.Stoplist

sys.fulltext_stoplists (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row per full-text stoplist in the database.

See sys.fulltext_stoplists.

FullTextSearchSchema.SystemStopWord

sys.fulltext_system_stopwords (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Provides access to the system stoplist.

See sys.fulltext_system_stopwords.

InformationSchema

InformationSchema.CheckConstraint

CHECK_CONSTRAINTS (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each CHECK constraint in the current database. This information schema view returns information about the objects to which the current user has permissions.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.CHECK_CONSTRAINTS.

InformationSchema.Column

COLUMNS (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each column that can be accessed by the current user in the current database.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA_.view_name_.

See INFORMATION_SCHEMA.COLUMNS.

InformationSchema.ColumnDomainUsage

COLUMN_DOMAIN_USAGE (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each column in the current database that has an alias data type. This information schema view returns information about the objects to which the current user has permissions.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE.

InformationSchema.ColumnPrivilege

COLUMN_PRIVILEGES (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each column that has a privilege that is either granted to or granted by the current user in the current database.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.COLUMN_PRIVILEGES.

InformationSchema.ConstraintColumnUsage

CONSTRAINT_COLUMN_USAGE (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each column in the current database that has a constraint defined on the column. This information schema view returns information about the objects to which the current user has permissions.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.

InformationSchema.ConstraintTableUsage

CONSTRAINT_TABLE_USAGE (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each table in the current database that has a constraint defined on the table. This information schema view returns information about the objects to which the current user has permissions.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.

InformationSchema.DataContext

InformationSchema.Domain

DOMAINS (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each alias data type that can be accessed by the current user in the current database.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.DOMAINS.

InformationSchema.DomainConstraint

DOMAIN_CONSTRAINTS (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each alias data type in the current database that has a rule bound to it and that can be accessed by current user.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS.

InformationSchema.KeyColumnUsage

KEY_COLUMN_USAGE (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each column that is constrained as a key in the current database. This information schema view returns information about the objects to which the current user has permissions.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

InformationSchema.Parameter

PARAMETERS (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database. For functions, this view also returns one row with return value information.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.PARAMETERS.

InformationSchema.ReferentialConstraint

REFERENTIAL_CONSTRAINTS (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each FOREIGN KEY constraint in the current database. This information schema view returns information about the objects to which the current user has permissions.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.

InformationSchema.Routine

ROUTINES (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each stored procedure and function that can be accessed by the current user in the current database. The columns that describe the return value apply only to functions. For stored procedures, these columns will be NULL.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA.*view_name*.

note

The ROUTINE_DEFINITION column contains the source statements that created the function or stored procedure. These source statements are likely to contain embedded carriage returns. If you are returning this column to an application that displays the results in a text format, the embedded carriage returns in the ROUTINE_DEFINITION results may affect the formatting of the overall result set. If you select the ROUTINE_DEFINITION column, you must adjust for the embedded carriage returns; for example, by returning the result set into a grid or returning ROUTINE_DEFINITION into its own text box.

See INFORMATION_SCHEMA.ROUTINES.

InformationSchema.RoutineColumn

ROUTINE_COLUMNS (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each column returned by the table-valued functions that can be accessed by the current user in the current database.
To retrieve information from this view, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.ROUTINE_COLUMNS.

InformationSchema.Schema

SCHEMATA (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each schema in the current database. To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_. To retrieve information about all databases in an instance of SQL Server, query the sys.databases (Transact-SQL) catalog view.

See INFORMATION_SCHEMA.SCHEMATA.

InformationSchema.Table

TABLES (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each table or view in the current database for which the current user has permissions.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.TABLES.

InformationSchema.TableConstraint

TABLE_CONSTRAINTS (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each table constraint in the current database. This information schema view returns information about the objects to which the current user has permissions.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

InformationSchema.TablePrivilege

TABLE_PRIVILEGES (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each table privilege that is granted to or granted by the current user in the current database.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.TABLE_PRIVILEGES.

InformationSchema.View

VIEWS (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for views that can be accessed by the current user in the current database.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.VIEWS.

InformationSchema.ViewColumnUsage

VIEW_COLUMN_USAGE (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each column in the current database that is used in a view definition. This information schema view returns information about the objects to which the current user has permissions.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.VIEW_COLUMN_USAGE.

InformationSchema.ViewTableUsage

VIEW_TABLE_USAGE (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each table in the current database that is used in a view. This information schema view returns information about the objects to which the current user has permissions.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA._view_name_.

See INFORMATION_SCHEMA.VIEW_TABLE_USAGE.

LinkedServersSchema

LinkedServersSchema.DataContext

LinkedServersSchema.LinkedLogin

sys.linked_logins (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per linked-server-login mapping, for use by RPC and distributed queries from local server to the corresponding linked server.

See sys.linked_logins.

LinkedServersSchema.RemoteLogin

sys.remote_logins (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per remote-login mapping. This catalog view is used to map incoming local logins that claim to be coming from a corresponding server to an actual local login.

See sys.remote_logins.

LinkedServersSchema.Server

sys.servers (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance

Contains a row per linked or remote server registered, and a row for the local server that has server_id = 0.

See sys.servers.

ObjectSchema

ObjectSchema.AllColumn

sys.all_columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Shows the union of all columns belonging to user-defined objects and system objects.

See sys.all_columns.

ObjectSchema.AllObject

sys.all_objects (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Shows the UNION of all schema-scoped user-defined objects and system objects.

See sys.all_objects.

ObjectSchema.AllParameter

sys.all_parameters (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Shows the union of all parameters that belong to user-defined or system objects.

See sys.all_parameters.

ObjectSchema.AllSqlModule

sys.all_sql_modules (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns the union of sys.sql_modules and sys.system_sql_modules.
The view returns a row for each natively compiled, scalar user-defined function. For more information, see Scalar User-Defined Functions for In-Memory OLTP.

See sys.all_sql_modules.

ObjectSchema.AllView

sys.all_views (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Shows the UNION of all user-defined and system views.

See sys.all_views.

ObjectSchema.AllocationUnit

sys.allocation_units (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row for each allocation unit in the database.

See sys.allocation_units.

ObjectSchema.AssemblyModule

sys.assembly_modules (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each function, procedure or trigger that is defined by a common language runtime (CLR) assembly. This catalog view maps CLR stored procedures, CLR triggers, or CLR functions to their underlying implementation. Objects of type TA, AF, PC, FS, and FT have an associated assembly module. To find the association between the object and the assembly, you can join this catalog view to other catalog views. For example, when you create a CLR stored procedure, it is represented by one row in sys.objects, one row in sys.procedures (which inherits from sys.objects), and one row in sys.assembly_modules. The stored procedure itself is represented by the metadata in sys.objects and sys.procedures. References to the procedure's underlying CLR implementation are found in sys.assembly_modules.

See sys.assembly_modules.

ObjectSchema.CheckConstraint

sys.check_constraints (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row for each object that is a CHECK constraint, with sys.objects.type = 'C'.

See sys.check_constraints.

ObjectSchema.Column

sys.columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns a row for each column of an object that has columns, such as views or tables. The following is a list of object types that have columns:
- Table-valued assembly functions (FT)
- Inline table-valued SQL functions (IF)
- Internal tables (IT)
- System tables (S)
- Table-valued SQL functions (TF)
- User tables (U)
- Views (V)

See sys.columns.

ObjectSchema.ColumnStoreDictionary

sys.column_store_dictionaries (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each dictionary used in xVelocity memory optimized columnstore indexes. Dictionaries are used to encode some, but not all data types, therefore not all columns in a columnstore index have dictionaries. A dictionary can exist as a primary dictionary (for all segments) and possibly for other secondary dictionaries used for a subset of the column's segments.

See sys.column_store_dictionaries.

ObjectSchema.ColumnStoreRowGroup

sys.column_store_row_groups (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Provides clustered columnstore index information on a per-segment basis to help the administrator make system management decisions. sys.column_store_row_groups has a column for the total number of rows physically stored (including those marked as deleted) and a column for the number of rows marked as deleted. Use sys.column_store_row_groups to determine which row groups have a high percentage of deleted rows and should be rebuilt.

See sys.column_store_row_groups.

ObjectSchema.ColumnStoreSegment

sys.column_store_segments (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns one row for each column segment in a columnstore index. There is one column segment per column per rowgroup. For example, a table with 10 rowgroups and 34 columns returns 340 rows.

See sys.column_store_segments.

ObjectSchema.ComputedColumn

sys.computed_columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each column found in sys.columns that is a computed-column.

See sys.computed_columns.

ObjectSchema.DataContext

ObjectSchema.DefaultConstraint

sys.default_constraints (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each object that is a default definition (created as part of a CREATE TABLE or ALTER TABLE statement instead of a CREATE DEFAULT statement), with sys.objects.type = D.

See sys.default_constraints.

ObjectSchema.Event

sys.events (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row for each event for which a trigger or event notification fires. These events represent the event types that are specified when the trigger or event notification is created by using CREATE TRIGGER or CREATE EVENT NOTIFICATION.

See sys.events.

ObjectSchema.EventNotification

sys.event_notifications (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns a row for each object that is an event notification, with sys.objects.type = EN.

See sys.event_notifications.

ObjectSchema.EventNotificationEventType

sys.event_notification_event_types (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns a row for each event or event group on which an event notification can fire.

See sys.event_notification_event_types.

ObjectSchema.ExtendedProcedure

sys.extended_procedures (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each object that is an extended stored procedure, with sys.objects.type = X. Because extended stored procedures are installed into the master database, they are only visible from that database context. Selecting from the sys.extended_procedures view in any other database context will return an empty result set.

See sys.extended_procedures.

ObjectSchema.ExternalLanguage

sys.external_languages (Transact-SQL)

Applies to: √ SQL Server 2019 (15.x)

This catalog view provides a list of the external languages in the database. R and Python are reserved names and no external language can be created with those specific names.
## sys.external_languages
The catalog view sys.external_languages lists a row for each external language in the database.

See sys.external_languages.

ObjectSchema.ExternalLanguageFile

sys.external_language_files (Transact-SQL)

Applies to: √ SQL Server 2019 (15.x)

This catalog view provides a list of the external language extension files in the database. R and Python are reserved names and no external language can be created with those specific names.
When an external language is created from a file_spec, the extension itself and its properties are listed in this view. This view will contain one entry per language, per OS.
## sys.external_language_files
The catalog view sys.external_language_files lists a row for each external language extension in the database. Parameters

See sys.external_language_files.

ObjectSchema.ExternalLibrary

sys.external_libraries (Transact-SQL)

Applies to: √ SQL Server 2017 (14.x) and later √ Azure SQL Managed Instance

Supports the management of package libraries related to external runtimes such as R, Python, and Java.

note

In SQL Server 2017, R language and Windows platform are supported. R, Python, and Java on the Windows and Linux platforms are supported in SQL Server 2019 and later. On Azure SQL Managed Instance, R and Python are supported.

## sys.external_libraries
The catalog view sys.external_libraries lists a row for each external library that has been uploaded into the database.

See sys.external_libraries.

ObjectSchema.ExternalLibraryFile

sys.external_library_files (Transact-SQL)

Applies to: √ SQL Server 2017 (14.x) and later √ Azure SQL Managed Instance

Lists a row for each file that makes up an external library.

See sys.external_library_files.

ObjectSchema.ForeignKey

sys.foreign_keys (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row per object that is a FOREIGN KEY constraint, with sys.object.type = F.

See sys.foreign_keys.

ObjectSchema.ForeignKeyColumn

sys.foreign_key_columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each column, or set of columns, that comprise a foreign key.

See sys.foreign_key_columns.

ObjectSchema.FunctionOrderColumn

sys.function_order_columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns one row per column that is a part of an ORDER expression of a common language runtime (CLR) table-valued function.

See sys.function_order_columns.

ObjectSchema.HashIndex

sys.hash_indexes (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Shows the current hash indexes and the hash index properties. Hash indexes are supported only on In-Memory OLTP (In-Memory Optimization).
The sys.hash_indexes view contains the same columns as the sys.indexes view and an additional column named bucket_count. For more information about the other columns in the sys.hash_indexes view, see sys.indexes (Transact-SQL).

See sys.hash_indexes.

ObjectSchema.IdentityColumn

sys.identity_columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each column that is an identity column.
The sys.identity_columns view inherits rows from the sys.columns view. The sys.identity_columns view returns the columns in the sys.columns view, plus the seed_value, increment_value, last_value, and is_not_for_replication columns. For more information, see Catalog Views (Transact-SQL).

See sys.identity_columns.

ObjectSchema.Index

sys.indexes (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

See sys.indexes.

ObjectSchema.IndexColumn

sys.index_columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains one row per column that is part of a sys.indexes index or unordered table (heap).

See sys.index_columns.

ObjectSchema.IndexResumableOperation

sys.index_resumable_operations (Transact-SQL)

Applies to: √ SQL Server 2017 (14.x) and later √ Azure SQL Database

sys.index_resumable_operations is a system view that monitors and checks the current execution status for resumable Index rebuild or creation.
Applies to: SQL Server (2017 and newer), and Azure SQL Database

See sys.index_resumable_operations.

ObjectSchema.InternalPartition

sys.internal_partitions (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance

Returns one row for each rowset that tracks internal data for columnstore indexes on disk-based tables. These rowsets are internal to columnstore indexes and track deleted rows, rowgroup mappings, and delta store rowgroups. They track data for each for each table partition; every table has at least one partition. SQL Server re-creates the rowsets each time it rebuilds the columnstore index.

See sys.internal_partitions.

ObjectSchema.InternalTable

sys.internal_tables (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns one row for each object that is an internal table. Internal tables are automatically generated by SQL Server to support various features. For example, when you create a primary XML index, SQL Server automatically creates an internal table to persist the shredded XML document data. Internal tables appear in the sys schema of every database and have unique, system-generated names that indicate their function, for example, xml_index_nodes_2021582240_32001 or queue_messages_1977058079
Internal tables do not contain user-accessible data, and their schema are fixed and unalterable. You cannot reference internal table names in Transact\-SQL statements. For example, you cannot execute a statement such as SELECT \* FROM *\<sys.internal_table_name>*. However, you can query catalog views to see the metadata of internal tables.

See sys.internal_tables.

ObjectSchema.KeyConstraint

sys.key_constraints (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each object that is a primary key or unique constraint. Includes sys.objects.type PK and UQ.

See sys.key_constraints.

ObjectSchema.MaskedColumn

sys.masked_columns (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance

Use the sys.masked_columns view to query for table-columns that have a dynamic data masking function applied to them. This view inherits from the sys.columns view. It returns all columns in the sys.columns view, plus the is_masked and masking_function columns, indicating if the column is masked, and if so, what masking function is defined. This view only shows the columns on which there is a masking function applied.

See sys.masked_columns.

ObjectSchema.MemoryOptimizedTablesInternalAttribute

sys.memory_optimized_tables_internal_attributes (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance

Contains a row for each internal memory-optimized table used for storing user memory-optimized tables. Each user table corresponds to one or more internal tables. A single table is used for the core data storage. Additional internal tables are used to support features such as temporal, columnstore index and off-row (LOB) storage for memory-optimized tables.

See sys.memory_optimized_tables_internal_attributes.

ObjectSchema.ModuleAssemblyUsage

sys.module_assembly_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each module-to-assembly reference.

See sys.module_assembly_usages.

ObjectSchema.NumberedProcedure

sys.numbered_procedures (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each SQL Server stored procedure that was created as a numbered procedure. This does not show a row for the base (number = 1) stored procedure. Entries for the base stored procedures can be found in views such as sys.objects and sys.procedures.

important

Numbered procedures are deprecated. Use of numbered procedures is discouraged. A DEPRECATION_ANNOUNCEMENT event is fired when a query that uses this catalog view is compiled.

See sys.numbered_procedures.

ObjectSchema.NumberedProcedureParameter

sys.numbered_procedure_parameters (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each parameter of a numbered procedure. When you create a numbered stored procedure, the base procedure is number 1. All subsequent procedures have numbers 2, 3, and so forth. sys.numbered_procedure_parameters contains the parameter definitions for all subsequent procedures, numbered 2 and greater. This view does not show parameters for the base stored procedure (number = 1). The base stored procedure is similar to a nonnumbered stored procedure. Therefore, its parameters are represented in sys.parameters (Transact-SQL).

important

Numbered procedures are deprecated. Use of numbered procedures is discouraged. A DEPRECATION_ANNOUNCEMENT event is fired when a query that uses this catalog view is compiled.

note

XML and CLR parameters are not supported for numbered procedures.

See sys.numbered_procedure_parameters.

ObjectSchema.Object

sys.objects (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.
For more information, see Scalar User-Defined Functions for In-Memory OLTP.

note

sys.objects does not show DDL triggers, because they are not schema-scoped. All triggers, both DML and DDL, are found in sys.triggers. sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers.

See sys.objects.

ObjectSchema.Parameter

sys.parameters (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each parameter of an object that accepts parameters. If the object is a scalar function, there is also a single row describing the return value. That row will have a parameter_id value of 0.

See sys.parameters.

ObjectSchema.Partition

sys.partitions (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each partition of all the tables and most types of indexes in the database. Special index types such as Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

See sys.partitions.

ObjectSchema.Period

sys.periods (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later

Returns a row for each table for which periods have been defined.

See sys.periods.

ObjectSchema.PlanGuide

sys.plan_guides (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row for each plan guide in the database.

See sys.plan_guides.

ObjectSchema.Procedure

sys.procedures (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each object that is a procedure of some kind, with sys.objects.type = P, X, RF, and PC.

See sys.procedures.

ObjectSchema.Sequence

sys.sequences (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row for each sequence object in a database.

See sys.sequences.

ObjectSchema.ServerAssemblyModule

sys.server_assembly_modules (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each assembly module for the server-level triggers of type TA. This view maps assembly triggers to the underlying CLR implementation. You can join this relation to sys.server_triggers. The assembly must be loaded into the master database. The tuple (object_id) is the key for the relation.

See sys.server_assembly_modules.

ObjectSchema.ServerEvent

sys.server_events (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each event for which a server-level event-notification or server-level DDL trigger fires. The columns object_id and type uniquely identify the server event.

See sys.server_events.

ObjectSchema.ServerEventNotification

sys.server_event_notifications (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each server-level event notification object.

See sys.server_event_notifications.

ObjectSchema.ServerSqlModule

sys.server_sql_modules (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains the set of SQL modules for server-level triggers of type TR. You can join this relation to sys.server_triggers. The tuple (object_id) is the key of the relation.

See sys.server_sql_modules.

ObjectSchema.ServerTrigger

sys.server_triggers (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains the set of all server-level DDL triggers with object_type of TR or TA. In the case of CLR triggers, the assembly must be loaded into the master database. All server-level DDL trigger names exist in a single, global scope.

See sys.server_triggers.

ObjectSchema.ServerTriggerEvent

sys.server_trigger_events (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each event for which a server-level (synchronous) trigger fires.

See sys.server_trigger_events.

ObjectSchema.SqlDependency

sys.sql_dependencies (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each dependency on a referenced entity as referenced in the Transact\-SQL expression or statements that define some other referencing object.

important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.sql_expression_dependencies instead.

See sys.sql_dependencies.

ObjectSchema.SqlExpressionDependency

sys.sql_expression_dependencies (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains one row for each by-name dependency on a user-defined entity in the current database. This includes dependences between natively compiled, scalar user-defined functions and other SQL Server modules. A dependency between two entities is created when one entity, called the *referenced entity*, appears by name in a persisted SQL expression of another entity, called the *referencing entity*. For example, when a table is referenced in the definition of a view, the view, as the referencing entity, depends on the table, the referenced entity. If the table is dropped, the view is unusable.
For more information, see Scalar User-Defined Functions for In-Memory OLTP.
You can use this catalog view to report dependency information for the following entities:
- Schema-bound entities.
- Non-schema-bound entities.
- Cross-database and cross-server entities. Entity names are reported; however, entity IDs are not resolved.
- Column-level dependencies on schema-bound entities. Column-level dependencies for non-schema-bound objects can be returned by using sys.dm_sql_referenced_entities.
- Server-level DDL triggers when in the context of the master database.

See sys.sql_expression_dependencies.

ObjectSchema.SqlModule

sys.sql_modules (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns a row for each object that is an SQL language-defined module in SQL Server, including natively compiled scalar user-defined function. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects catalog view.
For more information, see Scalar User-Defined Functions for In-Memory OLTP.

See sys.sql_modules.

ObjectSchema.Stat

sys.stats (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each statistics object that exists for the tables, indexes, and indexed views in the database in SQL Server. Every index will have a corresponding statistics row with the same name and ID (index_id = stats_id), but not every statistics row has a corresponding index.
The catalog view sys.stats_columns provides statistics information for each column in the database. For more information about statistics, see Statistics.

See sys.stats.

ObjectSchema.StatsColumn

sys.stats_columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each column that is part of sys.stats statistics.

See sys.stats_columns.

ObjectSchema.Synonym

sys.synonyms (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each synonym object that is sys.objects.type = SN.

See sys.synonyms.

ObjectSchema.SystemColumn

sys.system_columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each column of system objects that have columns.

See sys.system_columns.

ObjectSchema.SystemObject

sys.system_objects (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains one row for all schema-scoped system objects that are included with Microsoft SQL Server. All system objects are contained in the schemas named sys or INFORMATION_SCHEMA.

See sys.system_objects.

ObjectSchema.SystemParameter

sys.system_parameters (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains one row for each system object that has parameters.

See sys.system_parameters.

ObjectSchema.SystemSqlModule

sys.system_sql_modules (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row per system object that contains an SQL language-defined module. System objects of type FN, IF, P, PC, TF, V have an associated SQL module. To identify the containing object, you can join this view to sys.system_objects.

See sys.system_sql_modules.

ObjectSchema.SystemView

sys.system_views (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each system view that is shipped with SQL Server. All system views are contained in the schemas named sys or INFORMATION_SCHEMA.

See sys.system_views.

ObjectSchema.Table

sys.tables (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns a row for each user table in SQL Server.

See sys.tables.

ObjectSchema.TableType

sys.table_types (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Displays properties of user-defined table types in SQL Server. A table type is a type from which table variables or table-valued parameters could be declared. Each table type has a type_table_object_id that is a foreign key into the sys.objects catalog view. You can use this ID column to query various catalog views, in a way that is similar to an object_id column of a regular table, to discover the structure of the table type such as its columns and constraints.

See sys.table_types.

ObjectSchema.Trigger

sys.triggers (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row for each object that is a trigger, with a type of TR or TA. DML trigger names are schema-scoped and, therefore, are visible in sys.objects. DDL trigger names are scoped by the parent entity and are only visible in this view.
The parent_class and name columns uniquely identify the trigger in the database.

See sys.triggers.

ObjectSchema.TriggerEvent

sys.trigger_events (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Contains a row per event for which a trigger fires.

note

sys.trigger_events does not apply to event notifications.

See sys.trigger_events.

ObjectSchema.TriggerEventType

sys.trigger_event_types (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each event or event group on which a trigger can fire.

See sys.trigger_event_types.

ObjectSchema.View

sys.views (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each view object, with sys.objects.type = V.

See sys.views.

PartitionFunctionSchema

PartitionFunctionSchema.DataContext

PartitionFunctionSchema.PartitionFunction

sys.partition_functions (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each partition function in SQL Server.

See sys.partition_functions.

PartitionFunctionSchema.PartitionParameter

sys.partition_parameters (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each parameter of a partition function.

See sys.partition_parameters.

PartitionFunctionSchema.PartitionRangeValue

sys.partition_range_values (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each range boundary value of a partition function of type R.

See sys.partition_range_values.

PolicyBasedManagementSchema

PolicyBasedManagementSchema.Condition

syspolicy_conditions (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Displays one row for each Policy-Based Management condition in the instance of SQL Server. syspolicy_conditions belongs to the dbo schema in the msdb database. The following table describes the columns in the syspolicy_conditions view.

See dbo.syspolicy_conditions.

PolicyBasedManagementSchema.DataContext

PolicyBasedManagementSchema.Policy

syspolicy_policies (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Displays one row for each Policy-Based Management policy in the instance of SQL Server. syspolicy_policies belongs to the dbo schema in the msdb database. The following table describes the columns in the syspolicy_policies view.

See dbo.syspolicy_policies.

PolicyBasedManagementSchema.PolicyCategory

syspolicy_policy_categories (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Displays one row for each Policy-Based Management policy category in the instance of SQL Server. Policy categories help you organize policies when you have many policies. The following table describes the columns in the syspolicy_policy_groups view.

See dbo.syspolicy_policy_categories.

PolicyBasedManagementSchema.PolicyCategorySubscription

syspolicy_policy_category_subscriptions (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Displays one row for each Policy-Based Management subscription in the instance of SQL Server. Each row describes a target and policy category pair. The following table describes the columns in the syspolicy_policy_group_subscriptions view.

See dbo.syspolicy_policy_category_subscriptions.

PolicyBasedManagementSchema.PolicyExecutionHistory

syspolicy_policy_execution_history (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Displays the time when policies were executed, the result of each execution, and details about errors if any occurred. The following table describes the columns in the syspolicy_policy_execution_history view.

See dbo.syspolicy_policy_execution_history.

PolicyBasedManagementSchema.PolicyExecutionHistoryDetail

syspolicy_policy_execution_history_details (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Displays the condition expressions that were executed, the targets of the expressions, the result of each execution, and details about errors if any occurred. The following table describes the columns in the syspolicy_execution_history_details view.

See dbo.syspolicy_policy_execution_history_details.

PolicyBasedManagementSchema.SystemHealthState

syspolicy_system_health_state (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Displays one row for each Policy-Based Management policy and target query expression combination. Use the syspolicy_system_health_state view to programmatically check the policy health of the server. The following table describes the columns in the syspolicy_system_health_state view.

See dbo.syspolicy_system_health_state.

QueryStoreSchema

QueryStoreSchema.DataContext

QueryStoreSchema.DatabaseQueryStoreOption

sys.database_query_store_options (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Returns the Query Store options for this database.
Applies to: SQL Server (SQL Server 2016 (13.x) and later), SQL Database.

See sys.database_query_store_options.

QueryStoreSchema.QueryContextSetting

sys.query_context_settings (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Contains information about the semantics affecting context settings associated with a query. There are a number of context settings available in SQL Server that influence the query semantics (defining the correct result of the query). The same query text compiled under different settings may produce different results (depending on the underlying data).

See sys.query_context_settings.

QueryStoreSchema.QueryStorePlan

sys.query_store_plan (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Contains information about each execution plan associated with a query.

See sys.query_store_plan.

QueryStoreSchema.QueryStoreQuery

sys.query_store_query (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Contains information about the query and its associated overall aggregated runtime execution statistics.

See sys.query_store_query.

QueryStoreSchema.QueryStoreQueryHint

sys.query_store_query_hints (Transact-SQL)

Applies to: √ Azure SQL Database √ Azure SQL Managed Instance

Contains query hints from the Query Store hints (Preview) feature.

See sys.query_store_query_hints.

QueryStoreSchema.QueryStoreQueryText

sys.query_store_query_text (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Contains the Transact\-SQL text and the SQL handle of the query.

See sys.query_store_query_text.

QueryStoreSchema.QueryStoreRuntimeStat

sys.query_store_runtime_stats (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Contains information about the runtime execution statistics information for the query.

See sys.query_store_runtime_stats.

QueryStoreSchema.QueryStoreRuntimeStatsInterval

sys.query_store_runtime_stats_interval (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Contains information about the start and end time of each interval over which runtime execution statistics information for a query has been collected.

See sys.query_store_runtime_stats_interval.

QueryStoreSchema.QueryStoreWaitStat

sys.query_store_wait_stats (Transact-SQL)

Applies to: √ SQL Server 2017 (14.x) and later √ Azure SQL Database

Contains information about the wait information for the query.

See sys.query_store_wait_stats.

ResourceGovernorSchema

ResourceGovernorSchema.Configuration

sys.resource_governor_configuration (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns the stored Resource Governor state.

See sys.resource_governor_configuration.

ResourceGovernorSchema.DataContext

ResourceGovernorSchema.ExternalResourcePool

sys.resource_governor_external_resource_pools (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later

Applies to: SQL Server 2016 (13.x) R Services (In-Database) and SQL Server 2017 (14.x) Machine Learning Services
Returns the stored external resource pool configuration in SQL Server. Each row of the view determines the configuration of a pool.

See sys.resource_governor_external_resource_pools.

ResourceGovernorSchema.ResourcePool

sys.resource_governor_resource_pools (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns the stored resource pool configuration in SQL Server. Each row of the view determines the configuration of a pool.

See sys.resource_governor_resource_pools.

ResourceGovernorSchema.WorkloadGroup

sys.resource_governor_workload_groups (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns the stored workload group configuration in SQL Server. Each workload group can subscribe to one and only one resource pool.

See sys.resource_governor_workload_groups.

ScalarTypesSchema

ScalarTypesSchema.AssemblyType

sys.assembly_types (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each user-defined type that is defined by a CLR assembly. The following sys.assembly_types appear in the list of inherited columns (see sys.types (Transact-SQL)) after rule_object_id.

See sys.assembly_types.

ScalarTypesSchema.ColumnTypeUsage

sys.column_type_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each column that is of user-defined type.

See sys.column_type_usages.

ScalarTypesSchema.DataContext

ScalarTypesSchema.ParameterTypeUsage

sys.parameter_type_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns one row for each parameter that is of user-defined type.

note

This view does not return rows for parameters of numbered procedures.

See sys.parameter_type_usages.

ScalarTypesSchema.Type

sys.types (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Contains a row for each system and user-defined type.

See sys.types.

ScalarTypesSchema.TypeAssemblyUsage

sys.type_assembly_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row per type to assembly reference.

See sys.type_assembly_usages.

SecuritySchema

SecuritySchema.AsymmetricKey

sys.asymmetric_keys (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns a row for each asymmetric key.

See sys.asymmetric_keys.

SecuritySchema.Certificate

sys.certificates (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns a row for each certificate in the database.

See sys.certificates.

SecuritySchema.ColumnEncryptionKey

sys.column_encryption_keys (Transact-SQL)

APPLIES TO: (Yes) SQL Server 2016 and later (No) Azure SQL Database (Yes) Azure Synapse Analytics (No) Parallel Data Warehouse

Returns information about column encryption keys (CEKs) created with the CREATE COLUMN ENCRYPTION KEY statement. Each row represents a CEK.

See sys.column_encryption_keys.

SecuritySchema.ColumnEncryptionKeyValue

sys.column_encryption_key_values (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns information about encrypted values of column encryption keys (CEKs) created with either the CREATE COLUMN ENCRYPTION KEY or the ALTER COLUMN ENCRYPTION KEY (Transact-SQL) statement. Each row represents a value of a CEK, encrypted with a column master key (CMK).

See sys.column_encryption_key_values.

SecuritySchema.ColumnMasterKey

sys.column_master_keys (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance

Returns a row for each database master key added by using the CREATE MASTER KEY statement. Each row represents a single column master key (CMK).

See sys.column_master_keys.

SecuritySchema.Credential

sys.credentials (Transact-SQL)

APPLIES TO: (Yes) SQL Server (Yes) Azure SQL Managed Instance Azure Synapse Analytics (Yes) Parallel Data Warehouse

Returns one row for each server-level credential.

See sys.credentials.

SecuritySchema.CryptProperty

sys.crypt_properties (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each cryptographic property associated with a securable.

See sys.crypt_properties.

SecuritySchema.CryptographicProvider

sys.cryptographic_providers (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns one row for each registered cryptographic provider.

See sys.cryptographic_providers.

SecuritySchema.DataContext

SecuritySchema.DatabaseAuditSpecification

sys.database_audit_specifications (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about the database audit specifications in a SQL Server audit on a server instance. For more information, see SQL Server Audit (Database Engine).

See sys.database_audit_specifications.

SecuritySchema.DatabaseAuditSpecificationDetail

sys.database_audit_specification_details (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about the database audit specifications in a SQL Server audit on a server instance for all databases. For more information, see SQL Server Audit (Database Engine). For a list of all audit_action_id's and their names, query sys.dm_audit_actions (Transact-SQL).

See sys.database_audit_specification_details.

SecuritySchema.DatabaseCredential

sys.database_credentials (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Returns one row for each database scoped credential in the database.

important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.database_scoped_credentials instead.

See sys.database_credentials.

SecuritySchema.DatabaseLedgerBlock

sys.database_ledger_blocks (Transact-SQL)

Applies to: √ Azure SQL Database

Captures the cryptographically chained blocks, each of which represents a block of transactions against ledger tables.
For more information on database ledger, see Azure SQL Database ledger

See sys.database_ledger_blocks.

SecuritySchema.DatabaseLedgerDigestLocation

sys.database_ledger_digest_locations (Transact-SQL)

Applies to: √ Azure SQL Database

Captures the current and the historical ledger digest storage endpoints for the ledger feature.
For more information on database ledger, see Azure SQL Database ledger.

See sys.database_ledger_digest_locations.

SecuritySchema.DatabaseLedgerTransaction

sys.database_ledger_transactions (Transact-SQL)

Applies to: √ Azure SQL Database

Captures the cryptographically protected history of database transactions against ledger tables in the database. A row in this view represents a database transaction.
For more information on database ledger, see Azure SQL Database ledger.

See sys.database_ledger_transactions.

SecuritySchema.DatabasePermission

sys.database_permissions (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns a row for every permission or column-exception permission in the database. For columns, there is a row for every permission that is different from the corresponding object-level permission. If the column permission is the same as the corresponding object permission, there is no row for it and the permission applied is that of the object.

important

Column-level permissions override object-level permissions on the same entity.

See sys.database_permissions.

SecuritySchema.DatabasePrincipal

sys.database_principals (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns a row for each security principal in a SQL Server database.

See sys.database_principals.

SecuritySchema.DatabaseRoleMember

sys.database_role_members (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for each member of each database role. Database users, application roles, and other database roles can be members of a database role. To add members to a role, use the ALTER ROLE statement with the ADD MEMBER option. Join with sys.database_principals to return the names of the principal_id values.

See sys.database_role_members.

SecuritySchema.DatabaseScopedCredential

sys.database_scoped_credentials (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Returns one row for each database scoped credential in the database.
::: moniker range='=sql-server-2016'

See sys.database_scoped_credentials.

SecuritySchema.KeyEncryption

sys.key_encryptions (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns a row for each symmetric key encryption specified by using the ENCRYPTION BY clause of the CREATE SYMMETRIC KEY statement.

See sys.key_encryptions.

SecuritySchema.LedgerColumnHistory

sys.ledger_column_history (Transact-SQL)

Applies to: √ Azure SQL Database

Captures the cryptographically protected history of operations on columns of ledger tables: adding, renaming, and dropping columns.
For more information on database ledger, see Azure SQL Database ledger

See sys.ledger_column_history.

SecuritySchema.LedgerTableHistory

sys.ledger_table_history (Transact-SQL)

Applies to: √ Azure SQL Database

Captures the cryptographically protected history of operations on ledger tables: creating ledger tables, renaming ledger tables or ledger views, and dropping ledger tables.
For more information on database ledger, see Azure SQL Database ledger

See sys.ledger_table_history.

SecuritySchema.LoginToken

sys.login_token (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns one row for every server principal that is part of the login token.

See sys.login_token.

SecuritySchema.MasterKeyPassword

sys.master_key_passwords (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance

Returns a row for each database master key password added by using the sp_control_dbmasterkey_password stored procedure. The passwords that are used to protect the master keys are stored in the credential store. The credential name follows this format: ##DBMKEY_<database_family_guid>_<random_password_guid>##. The password is stored as the credential secret. For each password added by using sp_control_dbmasterkey_password, there is a row in sys.credentials.
Each row in this view shows a credential_id and the family_guid of a database the master key of which is protected by the password associated with that credential. A join with sys.credentials on the credential_id will return useful fields, such as the create_date and credential name.

See sys.master_key_passwords.

SecuritySchema.OpenKey

sys.openkeys (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

This catalog view returns information about encryption keys that are open in the current session.

See sys.openkeys.

SecuritySchema.SecurableClass

sys.securable_classes (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns a list of securable classes

See sys.securable_classes.

SecuritySchema.SecurityPolicy

sys.security_policies (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Returns a row for each security policy in the database.

See sys.security_policies.

SecuritySchema.SecurityPredicate

sys.security_predicates (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Returns a row for each security predicate in the database.

See sys.security_predicates.

SecuritySchema.SensitivityClassification

sys.sensitivity_classifications (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Returns a row for each classified item in the database.

See sys.sensitivity_classifications.

SecuritySchema.ServerAudit

sys.server_audits (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains one row for each SQL Server audit in a server instance. For more information, see SQL Server Audit (Database Engine).

See sys.server_audits.

SecuritySchema.ServerAuditSpecification

sys.server_audit_specifications (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about the server audit specifications in a SQL Server audit on a server instance. For more information on SQL Server Audit, see SQL Server Audit (Database Engine).

See sys.server_audit_specifications.

SecuritySchema.ServerAuditSpecificationDetail

sys.server_audit_specification_details (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains information about the server audit specification details (actions) in a SQL Server audit on a server instance. For more information, see SQL Server Audit (Database Engine). For a list of all audit_action_id's and their names, query sys.dm_audit_actions (Transact-SQL).

See sys.server_audit_specification_details.

SecuritySchema.ServerFileAudit

sys.server_file_audits (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains extended information about the file audit type in a SQL Server audit on a server instance. For more information, see SQL Server Audit (Database Engine).

See sys.server_file_audits.

SecuritySchema.ServerPermission

sys.server_permissions (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Analytics Platform System (PDW)

Returns one row for each server-level permission.

See sys.server_permissions.

SecuritySchema.ServerPrincipal

sys.server_principals (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Analytics Platform System (PDW)

Contains a row for every server-level principal.

See sys.server_principals.

SecuritySchema.ServerRoleMember

sys.server_role_members (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance √ Analytics Platform System (PDW)

Returns one row for each member of each fixed and user-defined server role.

See sys.server_role_members.

SecuritySchema.SqlLogin

sys.sql_logins (Transact-SQL)

APPLIES TO: (Yes) SQL Server (Yes) Azure SQL Database (No) Azure Synapse Analytics (Yes) Parallel Data Warehouse

Returns one row for every SQL Server authentication login.

See sys.sql_logins.

SecuritySchema.SymmetricKey

sys.symmetric_keys (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns one row for every symmetric key created with the CREATE SYMMETRIC KEY statement.

See sys.symmetric_keys.

SecuritySchema.SystemComponentsSurfaceAreaConfiguration

sys.system_components_surface_area_configuration (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns one row for each executable system object that can be enabled or disabled by a surface area configuration component. For more information, see Surface Area Configuration.

See sys.system_components_surface_area_configuration.

SecuritySchema.UserToken

sys.user_token (Transact-SQL)

APPLIES TO: (Yes) SQL Server (Yes) Azure SQL Database (Yes) Azure Synapse Analytics (No) Parallel Data Warehouse

Returns one row for every database principal that is part of the user token in SQL Server.

See sys.user_token.

ServerWideConfigurationSchema

ServerWideConfigurationSchema.Configuration

sys.configurations (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each server-wide configuration option value in the system.

See sys.configurations.

ServerWideConfigurationSchema.DataContext

ServerWideConfigurationSchema.TimeZoneInfo

sys.time_zone_info (Transact-SQL)

Applies to: √ SQL Server 2016 (13.x) and later √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics

Returns information about supported time zones. All time zones installed on the computer are stored in the following registry hive:
KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones.

See sys.time_zone_info.

ServerWideConfigurationSchema.Trace

sys.traces (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

The sys.traces catalog view contains the current running traces on the system. This view is intended as a replacement for the fn_trace_getinfo function.
For a complete list of supported trace events, see SQL Server Event Class Reference.

important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Event catalog views instead.

See sys.traces.

ServerWideConfigurationSchema.TraceCategory

sys.trace_categories (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Similar event classes are grouped by a category. Each row in the sys.trace_categories catalog view identifies a category that is unique across the server. These categories do not change for a given version of the SQL Server Database Engine.
For a complete list of supported trace events, see SQL Server Event Class Reference.
> IMPORTANT! This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Event catalog views instead.

See sys.trace_categories.

ServerWideConfigurationSchema.TraceColumn

sys.trace_columns (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

The sys.trace_columns catalog view contains a list of all trace event columns. These columns do not change for a given version of the SQL Server Database Engine.
For a complete list of supported trace events, see SQL Server Event Class Reference.

important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Event catalog views instead.

See sys.trace_columns.

ServerWideConfigurationSchema.TraceEvent

sys.trace_events (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

The sys.trace_events catalog view contains a list of all SQL trace events. These trace events do not change for a given version of the SQL Server Database Engine.
> IMPORTANT! This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Event catalog views instead.
For more information about these trace events, see SQL Server Event Class Reference.

See sys.trace_events.

ServerWideConfigurationSchema.TraceEventBinding

sys.trace_event_bindings (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

The sys.trace_event_bindings catalog view contains a list of all possible usage combinations of events and columns. For each event listed in the trace_event_id column, all available columns are listed in the trace_column_id column. Not all available columns are populated each time a given event occurs. These values do not change for a given version of the SQL Server Database Engine.
For a complete list of supported trace events, see SQL Server Event Class Reference.

important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Event catalog views instead.

See sys.trace_event_bindings.

ServerWideConfigurationSchema.TraceSubclassValue

sys.trace_subclass_values (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

The sys.trace_subclass_values catalog view contains a list of named column values. These subclass values do not change for a given version of the SQL Server Database Engine.
For a complete list of supported trace events, see SQL Server Event Class Reference.

important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Event catalog views instead.

See sys.trace_subclass_values.

ServiceBrokerSchema

ServiceBrokerSchema.ConversationEndpoint

sys.conversation_endpoints (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Each side of a Service Broker conversation is represented by a conversation endpoint. This catalog view contains a row per conversation endpoint in the database.

See sys.conversation_endpoints.

ServiceBrokerSchema.ConversationGroup

sys.conversation_groups (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view contains a row for each conversation group.

See sys.conversation_groups.

ServiceBrokerSchema.ConversationPriority

sys.conversation_priorities (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each conversation priority created in the current database, as shown in the following table:

See sys.conversation_priorities.

ServiceBrokerSchema.DataContext

ServiceBrokerSchema.MessageTypeXmlSchemaCollectionUsage

sys.message_type_xml_schema_collection_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view returns a row for each service message type that is validated by an XML schema collection.

See sys.message_type_xml_schema_collection_usages.

ServiceBrokerSchema.RemoteServiceBinding

sys.remote_service_bindings (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view contains a row per remote service binding.

See sys.remote_service_bindings.

ServiceBrokerSchema.Route

sys.routes (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Managed Instance

This catalog views contains one row per route. Service Broker uses routes to locate the network address for a service.

See sys.routes.

ServiceBrokerSchema.Service

sys.services (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view contains a row for each service in the database.

See sys.services.

ServiceBrokerSchema.ServiceContract

sys.service_contracts (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view contains a row for each contract in the database.

See sys.service_contracts.

ServiceBrokerSchema.ServiceContractMessageUsage

sys.service_contract_message_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view contains a row per (contract, message type) pair.

See sys.service_contract_message_usages.

ServiceBrokerSchema.ServiceContractUsage

sys.service_contract_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view contains a row per (service, contract) pair.

See sys.service_contract_usages.

ServiceBrokerSchema.ServiceMessageType

sys.service_message_types (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view contains a row per message type registered in the service broker.

See sys.service_message_types.

ServiceBrokerSchema.ServiceQueue

sys.service_queues (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Contains a row for each object in the database that is a service queue, with sys.objects.type = SQ.

See sys.service_queues.

ServiceBrokerSchema.ServiceQueueUsage

sys.service_queue_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view returns a row for each reference between service and service queue. A service can only be associated with one queue. A queue can be associated with multiple services.

See sys.service_queue_usages.

ServiceBrokerSchema.TransmissionQueue

sys.transmission_queue (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

This catalog view contains a row for each message in the transmission queue, as shown in the following table:

See sys.transmission_queue.

SpatialDataSchema

SpatialDataSchema.DataContext

SpatialDataSchema.SpatialIndex

sys.spatial_indexes (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Represents the main index information of the spatial indexes.

See sys.spatial_indexes.

SpatialDataSchema.SpatialIndexTessellation

sys.spatial_index_tessellations (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Represents the information about the tessellation scheme and parameters of each of the spatial indexes.

note

For information about tessellation, see Spatial Indexes Overview.

See sys.spatial_index_tessellations.

SpatialDataSchema.SpatialReferenceSystem

sys.spatial_reference_systems (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Lists the spatial reference systems (SRIDs) supported by SQL Server.

See sys.spatial_reference_systems.

StretchDatabaseSchema

StretchDatabaseSchema.DataContext

StretchDatabaseSchema.RemoteDataArchiveDatabase

Stretch Database Catalog Views - sys.remote_data_archive_databases

Applies to: √ SQL Server 2016 (13.x) and later

Contains one row for each remote database that stores data from a Stretch-enabled local database.

See sys.remote_data_archive_databases.

StretchDatabaseSchema.RemoteDataArchiveTable

Stretch Database Catalog Views - sys.remote_data_archive_tables

Applies to: √ SQL Server 2016 (13.x) and later

Contains one row for each remote table that stores data from a Stretch-enabled local table.

See sys.remote_data_archive_tables.

SystemDB

Database : master Data Source : . Server Version : 15.00.2101

SystemSchema

XmlSchema

XmlSchema.ColumnXmlSchemaCollectionUsage

sys.column_xml_schema_collection_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each column that is validated by an XML schema.

See sys.column_xml_schema_collection_usages.

XmlSchema.DataContext

XmlSchema.ParameterXmlSchemaCollectionUsage

sys.parameter_xml_schema_collection_usages (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns a row for each parameter that is validated by an XML schema.

See sys.parameter_xml_schema_collection_usages.

XmlSchema.SelectiveXmlIndexPath

sys.selective_xml_index_paths (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Available beginning in SQL Server 2012 (11.x) Service Pack 1, each row in sys.selective_xml_index_paths represents one promoted path for particular selective xml index.
If you create a selective xml index on xmlcol of table T using following statement,

CREATE SELECTIVE XML INDEX sxi1 ON T(xmlcol) 
FOR ( path1 = '/a/b/c' AS XQUERY 'xs:string',
    path2 = '/a/b/d' AS XQUERY 'xs:double'
  )

There will be two new rows in sys.selective_xml_index_paths corresponding to the index sxi1.

See sys.selective_xml_index_paths.

XmlSchema.XmlIndex

sys.xml_indexes (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns one row per XML index.

See sys.xml_indexes.

XmlSchema.XmlSchemaAttribute

sys.xml_schema_attributes (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per XML schema component that is an attribute, symbol_space of A.

See sys.xml_schema_attributes.

XmlSchema.XmlSchemaCollection

sys.xml_schema_collections (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database

Returns a row per XML schema collection. An XML schema collection is a named set of XSD definitions. The XML schema collection itself is contained in a relational schema, and it is identified by a schema-scoped Transact\-SQL name. The following tuples are unique: xml_collection_id, and schema_id and name.

See sys.xml_schema_collections.

XmlSchema.XmlSchemaComponent

sys.xml_schema_components (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per component of an XML schema. The pair (collection_id, namespace_id) is a compound foreign key to the containing namespace. For named components, the values for symbol_space, name, scoping_xml_component_id, is_qualified, xml_namespace_id, xml_collection_id are unique.

See sys.xml_schema_components.

XmlSchema.XmlSchemaComponentPlacement

sys.xml_schema_component_placements (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per placement for XML schema components.

See sys.xml_schema_component_placements.

XmlSchema.XmlSchemaElement

sys.xml_schema_elements (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per XML schema component that is a Type, symbol_space of E.

See sys.xml_schema_elements.

XmlSchema.XmlSchemaFacet

sys.xml_schema_facets (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per facet (restriction) of an xml-type definition (corresponds to sys.xml_types).

See sys.xml_schema_facets.

XmlSchema.XmlSchemaModelGroup

sys.xml_schema_model_groups (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per XML schema component that is a Model-Group, symbol_space of M..

See sys.xml_schema_model_groups.

XmlSchema.XmlSchemaNamespace

sys.xml_schema_namespaces (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per XSD-defined XML namespace. The following tuples are unique: collection_id, namespace_id, and collection_id, and name.

See sys.xml_schema_namespaces.

XmlSchema.XmlSchemaType

sys.xml_schema_types (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per XML schema component that is a Type, symbol_space of T.

See sys.xml_schema_types.

XmlSchema.XmlSchemaWildcard

sys.xml_schema_wildcards (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per XML schema component that is an Attribute-Wildcard (kind of V) or Element-Wildcard (kind of W), both with symbol_space of N.

See sys.xml_schema_wildcards.

XmlSchema.XmlSchemaWildcardNamespace

sys.xml_schema_wildcard_namespaces (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row per enumerated namespace for an XML schema wildcard.

See sys.xml_schema_wildcard_namespaces.