Posted by Roy Harvey on 07/12/07 01:16

For the second query it was actually a simplification to put each
index column on its own row.

SELECT o.name,
i.name, ik.keyno,
c.name
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
ORDER BY o.name, i.name, ik.keyno

I included the sequence number to help keep things clear and in order.

Hope that helps.

Roy Harvey
Beacon Falls, CT

On Wed, 11 Jul 2007 15:46:18 -0700, amonotod@netscape.net wrote:

>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]