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;





ċ
Andy Hughes,
Jun 5, 2020, 5:23 PM
ċ
Andy Hughes,
Jun 5, 2020, 5:23 PM
Comments