|
The following SQL can be used to query your database system tables for a list of indexes. With the exception
of Oracle, each query returns the index name with the appropriate schema\owner name appended to it.
Oracle simply returns the name of the index.
Sybase ASE
SELECT I.name AS index_name, B.name 'index_owner'
FROM sysindexes I,
syssegments S,
sysobjects O , sysusers B
WHERE I.indid>0
AND I.indid<255
AND I.status2 & 2!=2
AND I.segment=S.segment
AND O.id=I.id
AND B.uid = O.uid
AND B.name + '.' + O.name = :sTable
ORDER BY O.name
Oracle
SELECT index_name
FROM all_indexes
WHERE Owner = :sOwner
AND Table_Name = :sTable
SQL Server
IF OBJECT_ID('sys.schemas') IS NULL
SELECT I.name 'index_name', B.name 'index_owner'
FROM sysindexes I,
sysobjects O, sysusers B
WHERE I.indid>0
AND I.indid<255
AND O.id=I.id
AND O.type in ('U')
AND 1 != INDEXPROPERTY(i.id,i.name,N'IsStatistics')
AND 1 != INDEXPROPERTY(i.id,i.name,N'IsHypothetical')
AND B.uid = OBJECTPROPERTY ( O.id , 'ownerid' )
AND B.name + '.' + O.name = :sTable
ORDER BY B.name + '.' + O.name
ELSE
SELECT I.name 'index_name', B.name 'index_owner'
FROM sysindexes I,
sysobjects O , sys.schemas B
WHERE I.indid>0
AND I.indid<255
AND O.id=I.id
AND O.type in ('U')
AND 1 != INDEXPROPERTY(i.id,i.name,N'IsStatistics')
AND 1 != INDEXPROPERTY(i.id,i.name,N'IsHypothetical')
AND B.schema_id = O.uid
AND B.name + '.' + O.name = :sTable
ORDER BY B.name + '.' + O.name
SQL Anywhere
SELECT I.name as index_name, B.name 'index_owner'
FROM dbo.sysindexes I,
sysobjects O , sysusers B
WHERE I.indid>0
AND I.indid<255
AND I.status2 & 2!=2
AND O.id=I.id
AND O.uid = B.uid
AND B.name + '.' + O.name = :sTable
ORDER BY O.name
|
|
QweryBuilder is an SQL Editor and Query Tool. Its purpose is
to make accessing information from a database simple, quick, accurate and
efficient.
Work with your SQL Server, ASE, SQL Anywhere and Oracle databases in
one single application.
|
"QweryBuilder is a great, easy to use tool, that is a part of my everyday programmer life."
Read More Testimonials
|
|
|
