always forget some of the basic SQL Server catalogs, so this is going to help me next time:
Query to get the current index definitions from the database. Remember that you can cross reference this with some of this knowledge: https://shannonscncjdeblog.blogspot.com/2017/06/jde-slow-missing-indexes-find-it-fast.html (namely F98712 & F98713)
Remember that all index definitions do not need to be in specs (really – sorry if you disagree), performance based indexes can sit happily in the database and do not need to be put through the SDLC. Let's be honest, the creation process is horrendous in JDE!
The query below when cross referenced with F98712 and F98713 could tell you missing or incorrect indexes with a single statement. Though you could also run R9698711 and R9698713 to help you out.
select i.[name] as index_name,
substring(column_names, 1, len(column_names)-1) as [columns],
case when i.[type] = 1 then 'Clustered index'
when i.[type] = 2 then 'Nonclustered unique index'
when i.[type] = 3 then 'XML index'
when i.[type] = 4 then 'Spatial index'
when i.[type] = 5 then 'Clustered columnstore index'
when i.[type] = 6 then 'Nonclustered columnstore index'
when i.[type] = 7 then 'Nonclustered hash index'
end as index_type,
case when i.is_unique = 1 then 'Unique'
else 'Not unique' end as [unique],
schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type]
from sys.objects t
inner join sys.indexes i
on t.object_id = i.object_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
and ic.index_id = i.index_id
order by col.column_id
for xml path ('') ) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0
order by i.[name]
Provices something like
index_name columns index_type unique table_view object_type
F0000194_PK SYEDUS, SYEDBT, SYEDTN, SYEDLN Clustered index Unique NULL Table
F0002_PK NNSY Clustered index Unique NULL Table
F00021_PK NLKCO, NLDCT, NLCTRY, NLFY Clustered index Unique NULL Table
F00022_PK UKOBNM Clustered index Unique NULL Table
F0004_2 DTSY, DTRT, DTUSEQ Nonclustered unique index Unique NULL Table
F0004_PK DTSY, DTRT Clustered index Unique NULL Table
F0004D_PK DTSY, DTRT, DTLNGP Clustered index Unique NULL Table
F0005_2 DRSY, DRRT, DRKY, DRDL02 Nonclustered unique index Unique NULL Table
F0005_3 DRSY, DRRT, DRDL01 Nonclustered unique index Not unique NULL Table
F0005_PK DRSY, DRRT, DRKY Clustered index Unique NULL Table
SELECT
sOBJ.name AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
[RRCDBSP01\JDE920].[JDE_PRODUCTION].[sys].[objects] AS sOBJ
INNER JOIN [RRCDBSP01\JDE920].[JDE_PRODUCTION].[sys].[partitions] AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [TableName]
Tables that are not in JDE, but are in the database:
SELECT
sOBJ.name AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
JDE_PRODUCTION].[sys].[objects] AS sOBJ
INNER JOIN [JDE_PRODUCTION].[sys].[partitions] AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
AND not exists (select 1 from [JDE920].[OL920].[F9860] ol WHERE ol.siobnm = sOBJ.name and ol.sifuno = 'TBLE')
GROUP BY
sOBJ.schema_id
, sOBJ.name
Tables that are in:
SELECT
sOBJ.name AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
JDE_PRODUCTION].[sys].[objects] AS sOBJ
INNER JOIN [JDE_PRODUCTION].[sys].[partitions] AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
AND exists (select 1 from [JDE920].[OL920].[F9860] ol WHERE ol.siobnm = sOBJ.name and ol.sifuno = 'TBLE')
GROUP BY
sOBJ.schema_id
, sOBJ.name
Stolen from:
