Posted by amonotod on 07/11/07 22:46

Hello all,
I have a script which I'm hoping to use to accurately represent
online schemas in XML. It works great for Oracle, and I'm trying to
add in the MSSQL clauses now. I need help converting the below
working Oracle query to MSSQL...

select b.uniqueness, a.index_name, a.table_name, a.column_name,
a.column_position
from user_ind_columns a,
user_indexes b
where a.index_name=b.index_name
and a.index_name not in (select index_name from user_constraints
where constraint_type = 'P')
order by a.table_name, a.index_name, a.column_position

I've also got this query from Erland Sommarskog, but it doesn't
reliably list all index columns on a long index, and it also lists all
columns on a single line. I just don't know the data dictionary well
enough to manipulate the query to do what I need it to do...

SELECT o.name, i.name,
col1 = MIN (CASE ik.keyno WHEN 1 THEN c.name END),
col2 = MIN (CASE ik.keyno WHEN 2 THEN c.name END),
col3 = MIN (CASE ik.keyno WHEN 3 THEN c.name END),
col4 = MIN (CASE ik.keyno WHEN 4 THEN c.name END),
col5 = MIN (CASE ik.keyno WHEN 5 THEN c.name END)
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id
JOIN sysindexkeys ik ON ik.id = i.id
AND ik.indid = i.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(o.id, i.name, 'IsStatistics') = 0
AND indexproperty(o.id, i.name, 'IsHypothetical') = 0
GROUP BY o.name, i.name
ORDER BY o.name, i.name

So, if anyone could give me a hand, and get one of these queries
working to the point where I have an MSSQL output that matches the
Oracle output, I'd sure appreciate it....

Thanks!
amonotod

[Back to original message]