SQL Server 2008 System Functions to Monitor the Instance, Database, Files, etc.


By:   |   Updated: 2010-04-29   |   Comments (1)   |   Related: More > 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(VARCHARFILEPROPERTY('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


Last Updated: 2010-04-29


get scripts

next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, May 05, 2010 - 1:45:46 PM - AndreQ1 Back To Top

You could make it less hard coded with what is below, no? You could UNION ALL everything for a complete snapshot.

 create table LookUp (
 GroupName varchar(30),
 ItemName varchar(50)
)
declare @DatabaseName varchar(50)
select @DatabaseName = 'AdventureWorks'

declare @GroupName varchar(30)
select @GroupName = 'SERVERPROPERTY'

insert into LookUp (GroupName, ItemName)
select @GroupName, 'Collation'  union all
select @GroupName, 'Edition'   union all
select @GroupName,'ProductVersion'   union all
select @GroupName, 'ProductLevel'   union all
select @GroupName, 'MachineName'   union all
select @GroupName, 'InstanceName'   union all
select @GroupName, 'ServerName'   union all
select @GroupName, 'IsSingleUser'   union all
select @GroupName, 'IsClustered'

select lu.GroupName,
 lu.ItemName,
 SERVERPROPERTY(lu.ItemName) AS Value
from LookUp lu
where lu.GroupName = 'SERVERPROPERTY'
order by lu.ItemName
----------------------------------------------------------------

select @GroupName = 'DATABASEPROPERTYEX'
insert into LookUp (GroupName, ItemName)
SELECT @GROUPNAME,  'Collation' union all
SELECT @GROUPNAME,  'IsAutoShrink’ union all
SELECT @GROUPNAME,  'IsAutoUpdateStatistics’ union all
SELECT @GROUPNAME,  'Recovery’ union all
SELECT @GROUPNAME,  'Status’ union all
SELECT @GROUPNAME,  'IsAutoClose’ union all
SELECT @GROUPNAME,  'IsAutoCreateStatistics’ union all
SELECT @GROUPNAME,  'IsRecursiveTriggersEnabled’ union all
SELECT @GROUPNAME,  'Updateability’ union all
SELECT @GROUPNAME,  'UserAccess’

select lu.GroupName,
 lu.ItemName,
 DATABASEPROPERTYEX(@DatabaseName, lu.ItemName) AS Value
from LookUp lu
where lu.GroupName = 'DATABASEPROPERTYEX'
order by lu.ItemName
----------------------------------------------------------------

select @GroupName = 'FILEGROUPPROPERTY'
insert into LookUp (GroupName, ItemName)
SELECT @GROUPNAME,   'IsReadOnly' union all
SELECT @GROUPNAME,   'IsDefault' union all
SELECT @GROUPNAME,   'IsUserDefinedFG'
 
select lu.GroupName,
 lu.ItemName,
 FILEGROUPPROPERTY('PRIMARY', lu.ItemName) AS Value
from LookUp lu
where lu.GroupName = 'FILEGROUPPROPERTY'
order by lu.ItemName

----------------------------------------------------------------

select @GroupName = 'SESSIONPROPERTY'
insert into LookUp (GroupName, ItemName)
SELECT @GROUPNAME,   ‘ANSI_NULLS’ union all
SELECT @GROUPNAME,   ‘ANSI_PADDING’ union all
SELECT @GROUPNAME,   ‘ANSI_WARNINGS’ union all
SELECT @GROUPNAME,   ‘ARITHABORT’ union all
SELECT @GROUPNAME,   ‘CONCAT_NULL_YIELDS_ NULL’ union all
SELECT @GROUPNAME,   ‘NUMERIC_ROUNDABORT’ union all
SELECT @GROUPNAME,   ‘QUOTED_IDENTIFIER’

select lu.GroupName,
 lu.ItemName,
 SESSIONPROPERTY(lu.ItemName) AS Value
from LookUp lu
where lu.GroupName = 'SESSIONPROPERTY'
order by lu.ItemName

----------------------------------------------------------------

select @GroupName = 'CONNECTIONPROPERTY'
insert into LookUp (GroupName, ItemName)
SELECT @GROUPNAME,   ‘transport’ union all
SELECT @GROUPNAME,   ‘protocol_type’ union all
SELECT @GROUPNAME,   ‘auth_scheme’ union all
SELECT @GROUPNAME,   ‘local_net_address’ union all
SELECT @GROUPNAME,   ‘local_tcp_port’ union all
SELECT @GROUPNAME,   ‘client_net_address’

select lu.GroupName,
 lu.ItemName,
 CONNECTIONPROPERTY(lu.ItemName) AS Value
from LookUp lu
where lu.GroupName = 'CONNECTIONPROPERTY'
order by lu.ItemName

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools