-- Copyright (c) 2014, Lambert Boskamp -- All rights reserved. -- Redistribution and use in source and binary forms, with or without -- modification, are permitted provided that the following conditions -- are met: -- 1. Redistributions of source code must retain the above copyright -- notice, this list of conditions and the following disclaimer. -- 2. Redistributions in binary form must reproduce the above -- copyright notice, this list of conditions and the following -- disclaimer in the documentation and/or other materials provided -- with the distribution. -- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS -- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT -- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS -- FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE -- COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, -- INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES -- (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR -- SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) -- HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, -- STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) -- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED -- OF THE POSSIBILITY OF SUCH DAMAGE. -- -- ******************************************************************* -- -- Synopsis: Find any string in SAP(R) Identity Management (IDM) -- JavaScript or SQL source code, job definitions or tasks. -- -- THIS IS THE VERSION FOR MICROSOFT(R) SQL SERVER. -- -- The most recent version of this file as well as -- versions for other databases can be found at GitHub: -- -- https://github.com/boskamp/idmacs -- -- Usage: 1. Paste this source code into the SQL editor of any -- graphical SQL client that can display CLOB and/or -- XML data. Microsoft(R) SQL Server Management Studio, -- Oracle(R) SQL Developer and IBM(R) Data Studio are -- known to work fine. Others may work as well. -- -- 2. In the SQL editor, replace YOUR_SEARCH_TERM_HERE -- near the end of the code with the string you want -- to search for. See also section "Example". -- -- 3. Execute the resulting query as ADMIN user (MXMC_ADMIN). -- -- 4. (Optional) Examine MATCH_LOCATION_* and MATCH_DOCUMENT -- values of the result set directly in the SQL client. -- -- 5. (Optional) Locate tasks or jobs corresponding to NODE_ID -- values from your result set using MMC's Find action. -- Make sure to check "Find Tasks or Jobs only"! -- -- Example: To search for all occurences of the string MX_DISABLED, -- the code near the end should look like: -- -- where contains(upper-case($t),upper-case("MX_DISABLED")) -- -- Search is CASE-INSENSITIVE and uses SUBSTRING MATCHING, -- so this query would find any of the following strings, -- for instance: -- -- MX_DISABLED -- mx_disabled -- #mx_disabled -- -- Result: The result set will list any locations that contain your -- search term. Its rows have the following structure: -- -- 1. NODE_TYPE : char(1) -- 2. NODE_ID : int -- 3. NODE_NAME : varchar(max) -- 4. MATCH_LOCATION_XML : xml (MSSQL only) -- 5. MATCH_LOCATION_TEXT : varchar(max) -- 6. MATCH_DOCUMENT : xml -- -- NODE_TYPE = [ 'A' -- Attribute (Identity Store attribute) -- | 'T' -- Task -- | 'S' -- Script (global script) -- | 'J' -- Job -- ] -- -- NODE_ID = [ attribute_id -- | task_id -- | script_id -- | job_id -- ] -- -- NODE_NAME = [ attribute_name -- | task_name -- | script_name -- | job_name -- ] -- -- MATCH_LOCATION_XML is similar to MATCH_LOCATION_TEXT, -- just represented as XML to enable convenient hyperlink -- navigation in Microsoft(R) SQL Server Management Studio. -- This column exists in the MSSQL version only. -- -- MATCH_LOCATION_TEXT is a piece of text contained in -- MATCH_DOCUMENT. This piece of text contains your search -- term at least once. -- -- MATCH_DOCUMENT is an XML representation of the whole -- designtime object (attribute, task, script or job) -- identified by NODE_TYPE and NODE_ID. -- -- For MATCH_DOCUMENTs that contain your search term multiple -- times, the result set will generally contain multiple lines -- which differ only in their MATCH_LOCATION_* values. -- -- Credits: Martin Smith http://stackoverflow.com/users/73226/martin-smith -- Thanks for explaining how to display large text in SSMS -- -- Finn Ellebaek Nielsen https://ellebaek.wordpress.com -- Thanks for explaining how to convert LONG to CLOB on the fly -- -- ******************************************************************* with text_datasource_cte(node_id,node_type,node_name,native_xml) as ( select a.attr_id ,'A'-- Attribute ,a.attrname ,(select a.attr_id as "ATTR_ID" ,a.is_id as "IS_ID" ,i.idstorename as "IDSTORENAME" ,a.attrname as "ATTR_NAME" ,a.info as "INFO" ,a.deltask as "DELTASK" ,a.modtask as "MODTASK" ,a.instask as "INSTASK" ,a.display_name as "DISPLAY_NAME" ,a.tooltip as "TOOLTIP" ,a.regexvalidate as "REGEXVALIDATE" ,a.sqlvalues as "SQLVALUES" ,a.sqlaccesstask as "SQLACCESSTASK" ,a.sqlvaluestable as "SQLVALUESTABLE" ,a.sqlvaluesid as "SQLVALUESID" ,(select v.attr_value as "ATTR_VALUE" from mxi_attrvaluechoice v with (nolock) where v.attr_id=a.attr_id order by v.attr_value for xml path('ATTRIBUTE_VALUE_CHOICE_S') ,root('ATTRIBUTE_VALUE_CHOICE_T') ,type) for xml path('ATTRIBUTE_S') ,type) from mxiv_allattributes a with (nolock) inner join mxi_idstores i with (nolock) on a.is_id=i.is_id union all select t.taskid ,'T'-- Task ,t.taskname ,(select t.taskid as "TASKID" ,t.taskname as "TASKNAME" ,t.boolsql as "BOOLSQL" ,t.onsubmit as "ONSUBMIT" ,(select ta.attr_id as "ATTR_ID" ,ta.attrname as "ATTRNAME" ,ta.sqlvalues as "SQLVALUES" from mxiv_taskattributes ta with (nolock) where ta.taskid=t.taskid order by ta.attr_id for xml path('TASK_ATTRIBUTE_S') ,root('TASK_ATTRIBUTE_T') ,type) ,(select tx.sqlscript as "SQLSCRIPT" ,tx.targetsqlscript as "TARGETSQLSCRIPT" ,a.attrname as "ATTRNAME" ,ta.attrname as "TARGETATTRNAME" ,e.mcmskeyvalue as "MSKEYVALUE" ,te.mcmskeyvalue as "TARGETMSKEYVALUE" from mxpv_taskaccess tx with (nolock) left outer join mxi_attributes a with (nolock) on tx.attr_id=a.attr_id left outer join mxi_attributes ta with (nolock) on tx.targetattr_id=ta.attr_id left outer join idmv_entry_simple e with (nolock) on tx.mskey=e.mcmskey left outer join idmv_entry_simple te with (nolock) on tx.targetmskey=te.mcmskey where tx.taskid=t.taskid for xml path('TASK_ACCESS_S') ,root('TASK_ACCESS_T') ,type) order by t.taskid for xml path('TASK_S') -- Result set will always contain one task only, -- so let task be the root element, not tasks --,root('tasks') ,type) from mxpv_alltaskinfo t with (nolock) --Searching in stored procedure source code works only with MXMC_OPER. --object_definition() will always return NULL for other users, so the --query still works, but matches in stored procedure source code will --not be found. union all select object_id(routine_name) ,'P' ,routine_name ,(select routine_name as "PROCEDURE_NAME" ,object_definition( object_id( routine_name)) as "PROCEDURE_DEFINITION" for xml path('PROCEDURE_S') ,type) from information_schema.routines with (nolock) ) ,b64_enc_prefix_cte(node_id, node_type, node_name, b64_enc_prefix, is_xml) as ( select scriptid ,'S'--Global Script ,scriptname ,scriptdefinition ,0 from mc_global_scripts with (nolock) union all select jobid ,'J'--Job ,name ,jobdefinition ,1 from mc_jobs with (nolock) where jobdefinition is not null ) ,b64_enc_cte(node_id, node_type, node_name, b64_enc,is_xml)as ( select node_id ,node_type ,node_name -- Casting to varchar(max) before applying substring -- is required, otherwise B64_ENC_PREFIX will be truncated -- to 8000 bytes. ,substring( cast(b64_enc_prefix as varchar(max)) ,len('{B64}') + 1 ,datalength(b64_enc_prefix) - len('{B64}') ) ,is_xml from b64_enc_prefix_cte ) ,b64_dec_cte(node_id,node_type,node_name,b64_dec,is_xml) as ( select node_id ,node_type ,node_name ,cast(N'' AS XML).value( 'xs:base64Binary(sql:column("B64_ENC"))' ,'VARBINARY(MAX)' ) ,is_xml from b64_enc_cte ) ,xml_utf8_hex_cte(name,value) as ( select 'PROLOG' -- This is the hexadecimal byte values -- of the following UTF-8 encoded text: -- ,'5D5D3E3C2F524F4F543E' ) ,xml_utf8_bin_cte(name,value) as ( select name ,cast(N'' as XML).value( 'xs:hexBinary(sql:column("VALUE"))' , 'VARBINARY(MAX)' ) from xml_utf8_hex_cte ) ,b64_datasource_cte(node_id,node_type,node_name,native_xml) as ( select node_id ,node_type ,node_name ,case is_xml --If B64_DEC is not well-formed XML by itself, --decorate it with UTF-8 encoded XML prolog and epilog. --Casting UTF-8 binary to VARCHAR or NVARCHAR won't work. --See https://support.microsoft.com/en-us/kb/232580 when 0 then cast( (select value from xml_utf8_bin_cte where name='PROLOG') + b64_dec + (select value from xml_utf8_bin_cte where name='EPILOG') as xml) --If B64_DEC already is well-formed XML, --just cast it to T-SQL's native XML data type. else cast(b64_dec as xml) end from b64_dec_cte a ) ,any_datasource_cte(node_id,node_type,node_name,native_xml) as ( select * from b64_datasource_cte union all select * from text_datasource_cte ) select node_type ,node_id ,node_name --Column MATCH_LOCATION_XML is specific to the MSSQL version --of this query. It's only required to provide hyperlink --navigation and to preserve line breaks in SSMS. ,(select xml_sequence.value('.', 'VARCHAR(MAX)') as [processing-instruction(X)] for xml path('') ,type) as match_location_xml --Must use value() method of XML data type to avoid entitization --of XML characters, e.g. encoding of "<" to "<". ,xml_sequence.value('.', 'VARCHAR(MAX)') as match_location_text ,xml_sequence.query('/') as match_document from any_datasource_cte cross apply native_xml.nodes(' (: Release 2005 of MSSQL does not have fn:upper-case() yet. :) (: As a workaround, you can omit the upper-case() calls in the :) (: XQuery WHERE clause, resulting in case-sensitive search. :) for $t in (//attribute::*, /descendant-or-self::text()) where contains(upper-case($t),upper-case("YOUR_SEARCH_TERM_HERE")) return $t ') as t(xml_sequence) order by node_type,node_id ;