Help convert an Oracle select on indexes to MSSQL...?

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

Re: Help convert an Oracle select on indexes to MSSQL...?

For the first query the important change was to use NOT EXISTS rather
than NOT IN. NOT IN has some funky behavior when there is a NULL in
the result set, and I simply avoid NOT IN with any subquery as a
matter of principle. The change to use the JOIN syntax was optional.
In fact if the NOT IN was not in issue for your data there was really
nothing that had to change.

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

Re: Help convert an Oracle select on indexes to MSSQL...?

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

Re: Help convert an Oracle select on indexes to MSSQL...?

On Jul 11, 8:16 pm, Roy Harvey wrote:
> 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
>

Roy,
Thanks much for the help, I appreciate it! I was wondering... Can
you tell me why the query only shows Primary Key indexes? For the
databases I tested on, there are many additional indices that were
created with "create index..." that do not show up. Also, which field
would I query for the uniqueness attribute?

Thanks again,
amonotod

Re: Help convert an Oracle select on indexes to MSSQL...?

On Thu, 12 Jul 2007 11:02:52 -0700, amonotod@netscape.net wrote:

> Thanks much for the help, I appreciate it! I was wondering... Can
>you tell me why the query only shows Primary Key indexes? For the
>databases I tested on, there are many additional indices that were
>created with "create index..." that do not show up.

I have no idea, as it shows all indexes in my testing.

>Also, which field
>would I query for the uniqueness attribute?

indexproperty(o.id, i.name, 'IsUnique')

Roy Harvey
Beacon Falls, CT