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;