Problem
SQL Server provides several system meta data functions which allow users to obtain property values of different SQL Server objects and securables. In this tip I am going to demonstrate some of the available system meta data functions and their usage in different scenarios to .
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 function for SQL Server
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. Here is a complete list of the SERVERPROPERTY properties.
-- 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 functions for SQL Server
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. Here is a complete list of DATABASEPROPERTYEX properties.
-- 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 functions for SQL Server
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. Check these links for complete list of properties for FILEGROUPPROPERTY and FILEPROPERTY.
-- 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 functions for SQL Server
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 SESSIONPROPERTY and CONNECTIONPROPERTY.
-- 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:
