By: Arshad Ali | Comments (1) | Related: > Functions System
Problem
SQL Server provides several system meta data functions which allow users to obtain property values of different SQL Server objects and securables. Although you can also use the SQL Server catalog views or Dynamic Management Views to obtain much of this information, in some circumstances the system meta data functions simplify the process. In this tip I am going to demonstrate some of the available system meta data functions and their usage in different scenarios.
Solution
SQL Server provides several system meta data functions to obtain property values of different object types. Each system meta data function has a set of property list values you can query using the system meta functions, for example:
- SERVERPROPERTY
- DATABASEPROPERTY
- FILEGROUPPROPERTY
- FILEPROPERTY
- OBJECTPROPERTY
- COLUMNPROPERTY
- INDEXPROPERTY
- CONNECTIONPROPERTY
- SESSIONPROPERTY
In this tip, I am going to outline the some examples for the frequently used system meta data functions and their associated properties. Let's jump right in.
SERVERPROPERTY
The SERVERPROPERTY system meta data function provides different properties for the SQL Server instance. For example, to find out the edition of installed SQL Server you use 'Edition' property, to check if the SQL Server is in single user mode you use the 'IsSingleUser' property. Likewise to check if the SQL Server environment is clustered, you use 'IsClustered' property. Below is a script with the various properties of the SERVERPROPERTY system meta data function. For a complete list of the properties, click here.
-- Script #1 - SERVERPROPERTY System Meta Data Function
SELECT 'Collation' AS [ServerProperty], SERVERPROPERTY('Collation') AS [Value]
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
DATABASEPROPERTY and DATABASEPROPERTYEX
The DATABASEPROPERTY and DATABASEPROPERTYEX system meta data functions return similar information, the difference is DATABASEPROPERTY has been marked for deprecation and hence the recommendation is to use the DATABASEPROPERTYEX system meta data function. DATABASEPROPERTYEX system meta data function provides a number of properties for a specific database. For example, to find out the recovery model of the database you use the 'Recovery' property, to check if the database is in read/write mode you use the 'Updateability' property, to check if the database is in ONLINE, OFFLINE, RESTORING, RECOVERING, SUSPECT, EMERGENCY, etc. state you use 'Status' property. Below is a sample script, with this system meta data function with a number of different property options. For complete list of properties, click here.
-- Script #2 - DATABASEPROPERTYEX System Meta Data Function
SELECT 'Collation' AS [DatabaseProperty], DATABASEPROPERTYEX ('AdventureWorks', 'Collation') AS [Value]
UNION ALL
SELECT 'IsAutoShrink', DATABASEPROPERTYEX ('AdventureWorks', 'IsAutoShrink')
UNION ALL
SELECT 'IsAutoUpdateStatistics', DATABASEPROPERTYEX ('AdventureWorks', 'IsAutoUpdateStatistics')
UNION ALL
SELECT 'Recovery', DATABASEPROPERTYEX ('AdventureWorks', 'Recovery')
UNION ALL
SELECT 'Status', DATABASEPROPERTYEX ('AdventureWorks', 'Status')
UNION ALL
SELECT 'IsAutoClose', DATABASEPROPERTYEX ('AdventureWorks', 'IsAutoClose')
UNION ALL
SELECT 'IsAutoCreateStatistics', DATABASEPROPERTYEX ('AdventureWorks', 'IsAutoCreateStatistics')
UNION ALL
SELECT 'IsRecursiveTriggersEnabled', DATABASEPROPERTYEX ('AdventureWorks', 'IsRecursiveTriggersEnabled')
UNION ALL
SELECT 'Updateability', DATABASEPROPERTYEX ('AdventureWorks', 'Updateability')
UNION ALL
SELECT 'UserAccess', DATABASEPROPERTYEX ('AdventureWorks', 'UserAccess')
FILEGROUPPROPERTY and FILEPROPERTY
The FILEGROUPPROPERTY system meta data function provides information on a specific file group in the current database. For example, to find out if the file group is the default filegroup you, along with file group name, use the 'IsDefault' property. To check if the file group is in read only mode you use the 'IsReadOnly' property. To determine if the file group is a user defined file group you use the 'IsUserDefinedFG' property.
The FILEPROPERTY system meta data function provides information on the specified file in the current database. For example, to find out if the file is the primary file you use the 'IsPrimary' property. To check if the file is in read only mode you use the 'IsReadOnly' property. To check the number of pages in the given file you use the 'SpaceUsed' property. Check out the two scripts below as an example for each of these system meta data functions. For complete list of properties for the file group click here and for the file click here.
-- Script #3 - FILEGROUPPROPERTY and FILEPROPERTY System Functions
USE AdventureWorks
GO
SELECT 'IsReadOnly' AS [FilegroupProperty], FILEGROUPPROPERTY ('PRIMARY', 'IsReadOnly') AS [Value]
UNION ALL
SELECT 'IsDefault', FILEGROUPPROPERTY ('PRIMARY', 'IsDefault')
UNION ALL
SELECT 'IsUserDefinedFG', FILEGROUPPROPERTY ('PRIMARY', 'IsUserDefinedFG')
GO 
USE AdventureWorks
GO
SELECT 'IsPrimaryFile' AS [FileProperty], FILEPROPERTY('AdventureWorks_Data', 'IsPrimaryFile') AS [Value]
UNION ALL
SELECT 'IsReadOnly', FILEPROPERTY('AdventureWorks_Data', 'IsReadOnly')
UNION ALL
SELECT 'IsLogFile', FILEPROPERTY('AdventureWorks_Data', 'IsLogFile')
GO
SELECT 'SpaceUsed' AS [FileProperty], CONVERT(VARCHAR, FILEPROPERTY('AdventureWorks_Data', 'SpaceUsed')) + ' Pages allocated' AS [Value]
GO
SESSIONPROPERTY and CONNECTIONPROPERTY
SESSIONPROPERTY system meta function provides different properties option to grab SET options information for the current session whereas the CONNECTIONPROPERTY system meta function provides the different property options to grab connection property for the current connection. For example, using the SESSIONPROPERTY system meta data function you can get the set options for ANSI_NULLS, ARITHABORT, QUOTED_IDENTIFIER etc using respective property name, likewise using the CONNECTIONPROPERTY system meta data function you can get the protocol type used in the connection, authentication, etc. using the respective property name. For complete list of properties for session click here and for connection click here.
-- Script #4 - SESSIONPPROPERTY and CONNECTIONPROPERTY System Functions
SELECT 'ANSI_NULLS' AS [SessionProperty], SESSIONPROPERTY('ANSI_NULLS') AS [Value]
UNION ALL
SELECT 'ARITHABORT', SESSIONPROPERTY('ARITHABORT')
UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL', SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL')
UNION ALL
SELECT 'QUOTED_IDENTIFIER', SESSIONPROPERTY('QUOTED_IDENTIFIER')
UNION ALL
SELECT 'NUMERIC_ROUNDABORT', SESSIONPROPERTY('NUMERIC_ROUNDABORT') 
SELECT 'net_transport' AS [ConnectionProperty], CONNECTIONPROPERTY('net_transport') AS [Value]
UNION ALL
SELECT 'protocol_type', CONNECTIONPROPERTY('protocol_type')
UNION ALL
SELECT 'auth_scheme', CONNECTIONPROPERTY('auth_scheme')
UNION ALL
SELECT 'client_net_address', CONNECTIONPROPERTY('client_net_address')
Next Steps
- Test these scripts out in your environment and compare them to the catalog views and dynamic management views.
- Stay tuned for a second part to this tip.
- Review the following tips:
About the author
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
