sqlbelle's musings

We had to trace which among our hundreds of Blackbaud Enterprise CRM (BBEC) ad hoc queries were using Site from the Designation node. We also needed to figure out if they were being used as output or being used as filter.

Here’s the script that did the trick.

-- BBEC Ad Hoc Queries that use SITE in DESIGNATION
WITH XMLNAMESPACES(DEFAULT N'Blackbaud.AppFx.WebService.API.1')
SELECT
      A.NAME,
      AP.USERNAME [OWNER],
      A.DESCRIPTION,
      CASE
      WHEN QUERYDEFINITIONXML.exist('//SelectFields/f[@ObjectName="V_QUERY_DESIGNATIONLEVEL"][@ColumnName="SITENAME"]') = 1
      THEN 'Output'
      ELSE ''
      END USED_SELECTFIELD,
      CASE
      WHEN QUERYDEFINITIONXML.exist('//FilterFields/f[@ObjectName="V_QUERY_DESIGNATIONLEVEL"][@ColumnName="SITENAME"]') = 1
      THEN 'Filter'
      ELSE ''
      END USED_FILTERFIELD,
      A.DATEADDED,
      A.DATECHANGED
FROM
      ADHOCQUERY A
      LEFT JOIN APPUSER AP
      ON A.OWNERID = AP.ID
WHERE
      QUERYDEFINITIONXML.exist('//f[@ObjectName="V_QUERY_DESIGNATIONLEVEL"][@ColumnName="SITENAME"]') = 1
ORDER BY
      A.NAME