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

SELECT

ob.name AS Queue_Name

,COALESCE(EP.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS EP

ON EP.major_id = ob.object_id

AND 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

SELECT

ob.name AS Rule_Name --old-fashioned sybase-style rule

,COALESCE(EP.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS EP

ON EP.major_id = ob.object_id

AND EP.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id, 'IsRule') = 1;