I know its an old thread, but thanks Devon for this code. I have since modified further to include the path for a recent requirement of mine. Thought of posting here for others who may need it.
SELECT
N1.CMID,
N1.NAME "Report Name"
--,N10.NAME+'/'+N9.NAME+'/'+N8.NAME+'/'+N7.NAME+'/'+N6.NAME+'/'+N5.NAME+'/'+N4.NAME+'/'+N3.NAME+'/'+N2.NAME+'/'+N1.NAME AS PATH
,REPLACE((N10.NAME+'/'+N9.NAME+'/'+N8.NAME+'/'+N7.NAME+'/'+N6.NAME+'/'+N5.NAME+'/'+N4.NAME+'/'+N3.NAME+'/'+N2.NAME+'/'+N1.NAME),'//','') AS PATH2
FROM CMOBJECTS O1, CMOBJNAMES N1,
CMOBJECTS O2, CMOBJNAMES N2,
CMOBJECTS O3, CMOBJNAMES N3,
CMOBJECTS O4, CMOBJNAMES N4,
CMOBJECTS O5, CMOBJNAMES N5,
CMOBJECTS O6, CMOBJNAMES N6,
CMOBJECTS O7, CMOBJNAMES N7,
CMOBJECTS O8, CMOBJNAMES N8,
CMOBJECTS O9, CMOBJNAMES N9,
CMOBJECTS O10, CMOBJNAMES N10,
CMCLASSES C
where O2.CMID=O1.PCMID AND O3.CMID=O2.PCMID AND O4.CMID=O3.PCMID
AND O5.CMID=O4.PCMID AND O6.CMID=O5.PCMID AND O7.CMID=O6.PCMID
AND O8.CMID=O7.PCMID AND O9.CMID=O8.PCMID AND O10.CMID=O9.PCMID
AND O1.CMID= N1.CMID
AND O2.CMID= N2.CMID
AND O3.CMID= N3.CMID
AND O4.CMID= N4.CMID
AND O5.CMID= N5.CMID
AND O6.CMID= N6.CMID
AND O7.CMID= N7.CMID
AND O8.CMID= N8.CMID
AND O9.CMID= N9.CMID
AND O10.CMID= N10.CMID
AND O1.CLASSID = C.CLASSID
AND N1.ISDEFAULT=1
AND C.NAME ='report'
AND (N1.LOCALEID IN (92,118)
AND N2.LOCALEID IN (92,118)
AND N3.LOCALEID IN (92,118)
AND N4.LOCALEID IN (92,118)
AND N5.LOCALEID IN (92,118)
AND N6.LOCALEID IN (92,118)
AND N7.LOCALEID IN (92,118)
AND N8.LOCALEID IN (92,118)
AND N9.LOCALEID IN (92,118)
AND N10.LOCALEID IN (92,118)
)
AND O1.CMID IN
(
SELECT DISTINCT CMID FROM (
select
s.CMID,
try_convert(xml,s.SPEC).value('count(//*[local-name()="sqlText"])', 'int') CustomSQL
from CMOBJPROPS7 s where s.spec is not null
) A
WHERE A.CustomSQL>0
)
Note: Identifying the path is difficult because of the layers of CMID and PCMID's where the report is buried. This query finds the path in a brute force manner down to 9 levels from root folder. If the report is buried in deeper layers, the query will only give bottom 9 layers/folders.
------------------------------
JAGANNADHA VENU GOPAL ANASAPURAPU
------------------------------
Original Message:
Sent: Wed April 26, 2023 12:57 PM
From: Devon Ward
Subject: How to find Cognos reports with embedded SQL ?
Using Microsoft SQL Server the following SQL against the Cognos Content Store database will count the number of embedded SQL queries in each report. Technically CMOBJPROPS7 includes all cognos objects with a specification, which includes dashboards (aka explorations), but those specifications are in JSON format (not XML), so that is why we use try_convert(xml...) instead of just convert(xml...). You can probably ignore any of the records returned in the results where the CustomSQL column is null, which I assumed means those are non-report objects (not xml specifications)
select
s.CMID,
try_convert(xml,s.SPEC).value('count(//*[local-name()="sqlText"])', 'int') CustomSQL
from CMOBJPROPS7 s where s.spec is not null
------------------------------
Devon Ward
Original Message:
Sent: Tue April 25, 2023 05:05 PM
From: Sandip Mondal
Subject: How to find Cognos reports with embedded SQL ?
Hi,
How to find Cognos reports with embedded SQL ? Is there any SQL we can run on the content schema ?
Thank you,
Sandip
------------------------------
Sandip Mondal
------------------------------