Thought this might be useful for someone...
We're moving to K2 Cloud and I have a SmartObject that needs to be updated. K2 will tell you which views are built on a SmartObject but not the SmartObjects that are used in the rules.
This SQL will return the usages to you but if you change the last line to a guid like a View or Form GUID, it will show the usages of those objects as well.
SELECT [ContextID], [VIEW].NAME [View], Form.Name Form, A.DisplayValue, ValueFROM(SELECT [ContextID], prop.query('data(Value)') as Value, prop.query('data(DisplayValue)') as DisplayValueFROM [Form].[ActionItem] ACROSS APPLY Properties.nodes('Property') as x(prop)) ALEFT JOIN [Form].[View] ON [View].ID=A.ContextIDLEFT JOIN [Form].[Form] Form ON [Form].ID=A.ContextIDWHERE CONVERT(VARCHAR(MAX),A.VALUE)= (SELECT TOP 1 CONVERT(VARCHAR(38),SmartObjectGUID) FROM SmartBroker.SmartObject WHERE DisplayName_XML LIKE 'MySmartObjectName' ORDER BY Version DESC)If you need to search checked out items, change
FROM [Form].[ActionItem] A
to
FROM [Form].[ActionItem_Design] A
If the views have never been checked in you need to add _Design here as well
LEFT JOIN [Form].[View_Design] ON [View].ID=A.ContextID
LEFT JOIN [Form].[Form_Design] Form ON [Form].ID=A.ContextID
It's also helpful to be able to search the Parameters, this would let you find usages of the left and right side of a transfer data, for instance an environment field.
SELECT DISTINCT [ContextID], [VIEW].NAME [View], Form.Name FormFROM [Form].[ActionItem] ALEFT JOIN [Form].[View] ON [View].ID=A.ContextIDLEFT JOIN [Form].[Form] Form ON [Form].ID=A.ContextIDWHERE try_convert(varchar(max),A.Parameters) LIKE '%SmartForms Runtime SSL (default)%' OR try_convert(varchar(max),A.Properties) LIKE '%SmartForms Runtime SSL (default)%' --The where's like could also be LIKE (SELECT '%'+CONVERT(VARCHAR(38),ID)+'%' FROM Form.[View] WHERE DisplayName='MyNiceView')LIKE (SELECT '%'+CONVERT(VARCHAR(38),ID)+'%' FROM Form.[Form] WHERE DisplayName='MyFantasticForm')
Find usages in Forms and Views
DECLARE @SearchTerm VARCHAR(128)='SSRS'--Usages in FormsSELECT a.ID, Name Form, Parameters, Properties, Expressions FROM [Form].[Form] A WHERE try_convert(varchar(max),A.Parameters) LIKE '%'+@SearchTerm+'%' OR try_convert(varchar(max),A.Properties) LIKE '%'+@SearchTerm+'%' OR try_convert(varchar(max),A.Expressions) LIKE '%'+@SearchTerm+'%'--Usages in ViewsSELECT a.ID, Name [View], Parameters, Properties, Expressions FROM [Form].[View] A WHERE try_convert(varchar(max),A.Parameters) LIKE '%'+@SearchTerm+'%' OR try_convert(varchar(max),A.Properties) LIKE '%'+@SearchTerm+'%' OR try_convert(varchar(max),A.Expressions) LIKE '%'+@SearchTerm+'%'
To fully search you need to look in the following tables and fields
[Form].[HandlerInstance] Properties and Functions
[Form].[State] Properties
[Form].[Control] Properties and Styles
[Form].[ConditionItem] Properties and Expressions
Update 18 Aug 20: Added wildcard search in Properties to second search.
Update 24 Aug 20: Search in Form and View definitions
If this helps, maybe hit the kudos. :)
