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@xxxxxxxxxxxx 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
.

Relevant Pages

  • Re: Help convert an Oracle select on indexes to MSSQL...?
    ... index column on its own row. ... FROM sysobjects o ... JOIN sysindexes i ON i.id = o.id ... you tell me why the query only shows Primary Key indexes? ...
    (comp.databases.ms-sqlserver)
  • Re: Reformat Group membership table
    ... CROSS JOIN sysobjects s2 ... I would like to run SQL queries that returns a result set which has all ... believe I can do this effectively is to reformat the table to a format ... I'm looking for suggestions on how I can accomplish my SQL query... ...
    (microsoft.public.sqlserver.programming)
  • select no. of rows from the current database
    ... the result of the query is (depending of the database): ... But when I count the number of rows of "sysobjects", ...
    (comp.databases.ms-sqlserver)
  • Re: Help convert an Oracle select on indexes to MSSQL...?
    ... For the first query the important change was to use NOT EXISTS rather ... It works great for Oracle, ... JOIN sysindexes i ON i.id = o.id ... working to the point where I have an MSSQL output that matches the ...
    (comp.databases.ms-sqlserver)
  • RE: syntax to print list of SPs
    ... select name from sysobjects where type='u' ... For Stored Procedure ... "Vyshnavi" wrote: ... > This query will give you the objects shipped by Microsoft during installation. ...
    (microsoft.public.sqlserver.programming)
Loading