Fw check system option guids
Overview
SP will:
- find all system options which appear to be GUIDs (i.e. '9421d3e7-704f-4add-b52d-9e32378967c5')
- search each database table for the GUID, report results
This SP does not ship in baseline netFORUM. If you want to use this SP, then you must add it to your database.
Change Log
| Change Date | Change User | Notes |
|---|---|---|
| 11/12/2007 | jvitray | Original Version |
SP Text
CREATE procedure fw_check_system_option_guids AS declare @n int, @m int, @sql varchar(2000) CREATE TABLE #fws_key ( fws_option nvarchar(160), fws_value nvarchar(2000), mdc_mdt_name nvarchar(256), mdc_name nvarchar(256), found bit DEFAULT 0) CREATE TABLE #table_and_key( mdc_id int identity(1,1), mdc_mdt_name nvarchar(256), mdc_name nvarchar(256)) INSERT #fws_key (fws_option, fws_value)SELECT fws_option, fws_value FROM fw_system_optionWHERE fws_value LIKE '________-____-____-____-____________' INSERT #table_and_key (mdc_mdt_name, mdc_name)SELECT so.name, sc.name FROM sysobjects soJOIN syscolumns scON so.id = sc.idAND so.name NOT IN ('co_customer_activity','md_page_access')JOIN md_tableON mdt_name = so.nameJOIN sysobjects pkON so.id = pk.parent_obj AND pk.xtype = 'PK'AND pk.uid = sc.colidORDER BY datalength(sc.name) DESC -- Search sub-tables first SELECT @m = max(mdc_id) FROM #table_and_key SELECT @n = 1 while @n <= @m beginSELECT @sql = 'Update #fws_key set found = 1, mdc_mdt_name = ''' + mdc_mdt_name + ''', mdc_name = ''' + mdc_name + ''' where found = 0 and fws_value in (Select ' + mdc_name + ' from ' + mdc_mdt_name + ')'FROM #table_and_key where mdc_id = @n --print @sql exec(@sql) SELECT @n = @n + 1 end SELECT * FROM #fws_keyORDER BY 5,1
More
Darryl's code that finds system options and templates:
declare @val nvarchar(1000)SET @val = 'OrderFulfillmentConfirmationEmailTemplate'SELECT *FROM md_dynamic_form_extensionWHERE dyx_object_initialize_parameters LIKE '%' + @val + '%'OR dyx_object_load_parameters LIKE '%' + @val + '%'OR dyx_object_after_save_parameters LIKE '%' + @val + '%'OR dyx_object_before_save_parameters LIKE '%' + @val + '%' SELECT dyn_title, dyn_description, *FROM md_dynamic_form_controlJOIN md_dynamic_form ON dyn_key = dys_dyn_keyWHERE dys_default_value LIKE '%' + @val + '%' SELECT cct_code, *FROM fw_system_option LEFT JOIN co_correspondence_template ON convert(char(36), cct_key) = fws_valueWHERE fws_option = @val