Get Queues and Rules Metadata

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author:Phil Factor/ChillyDBA

Date: 5 Jun 2020

Description

Metadata queries for Database Queues and Legacy Style Database Rules

Code

Get a detail list of all Database Queues:

/* The Queues */--all queues in the database SELECTob.name AS Queue_Name,COALESCE(EP.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND EP.name = 'MS_Description' --the microsoft convention WHERE OBJECTPROPERTY(ob.object_id, 'IsQueue') = 1;

Get a detail list of all Database Rules:

/* The Rules */--all old-style rules in the database SELECTob.name AS Rule_Name --old-fashioned sybase-style rule ,COALESCE(EP.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND EP.name = 'MS_Description' --the microsoft convention WHERE OBJECTPROPERTY(ob.object_id, 'IsRule') = 1;