Need a report listing custom properties and their uses

borgan

I need to delete a number of custom properties from my database, but I do not want to do it until I have an idea of how those CPs are being used. So, how would I create a report showing the views, groups, limitations, reports, alerts, etc where a CP is currently used?

Thanks for any help as I believe this will be a pretty hairy SQL job.

mesverrum

This is the stack of SQL queries I use to make reports on properties that i want to delete:

--views and resources

SELECT cp.Name as [Property Name], v.viewgroupname, v.viewtitle, r.resourcetitle, r.resourcename, rp.propertyvalue

FROM sys.columns cp

left join [dbo].[ResourceProperties] rp on propertyvalue like '%'+cp.Name+'%'

join resources r on r.resourceid=rp.resourceid

join views v on v.viewid=r.viewid

WHERE object_id = OBJECT_ID('NodesCustomProperties')

and cp.name != 'NodeID'

order by cp.name

--alert actions

SELECT cp.Name as [Property Name], a.title, ap.propertyname, ap.propertyvalue

FROM sys.columns cp

left join [dbo].[ActionsProperties] AP on PROPERTYVALUE like '%'+cp.Name+'%'

join ACTIONS A ON A.ACTIONID=AP.ACTIONID

WHERE object_id = OBJECT_ID('NodesCustomProperties')

and cp.name != 'NodeID'

order by cp.name

--alert configurations

SELECT cp.Name as [Property Name], ac.name as Alertname

FROM sys.columns cp

Left join [dbo].[AlertConfigurations] ac on ([trigger] like '%'+cp.Name+'%' or reset like '%'+cp.Name+'%')

WHERE object_id = OBJECT_ID('NodesCustomProperties')

and cp.name != 'NodeID'

and ac.enabled=1

order by cp.name

--groups

SELECT cp.Name as [Property Name], c.Name as [Group], cm.name as [Rule Name], cm.expression, cm.definition

FROM sys.columns cp

join [dbo].[ContainerMemberDefinitions] cm on cm.expression like '%'+cp.Name+'%' or cm.definition like '%'+cp.Name+'%'

join containers c on c.containerid=cm.containerid

WHERE object_id = OBJECT_ID('NodesCustomProperties')

and cp.name != 'NodeID'

order by cp.name

--view and account limitations

SELECT cp.Name as [Property Name], l.whereclause, v2.viewgroupname, v2.viewtitle, acc.menuname, acc.limitationid1, acc.limitationid2, acc.limitationid3

FROM sys.columns cp

join [dbo].[Limitations] l on l.whereclause like '%'+cp.Name+'%'

join dbo.views v2 on v2.limitationid=l.limitationid

join dbo.accounts acc on acc.limitationid1=l.limitationid or acc.limitationid2=l.limitationid or acc.limitationid3=l.limitationid

order by cp.name

--reports

SELECT cp.Name as [Property Name], rep.name, rep.definition

FROM sys.columns cp

join  [dbo].[ReportDefinitions] rep on rep.definition like '%'+cp.Name+'%'

WHERE object_id = OBJECT_ID('NodesCustomProperties')

and cp.name != 'NodeID'

order by cp.name