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

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

[Back to original message]