SQL QUERY GUIDANCE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL QUERY GUIDANCE

Author

Topic

ajh
Starting Member

3 Posts

Posted - 2011-05-05 : 09:37:28

Example 1

Schemna name: XYZ
Index name : I1
Table name : A
columns name : col1, col2

Schema name : PQR
Index name : I8
Table name : A
columns name : col1, col2

Query Should Return :

Index Name Table Columns
I1 A col1 , col2
I8 A col1 , col2

This means that 2 indexes in 2 different schemas are based on the same columns. I need to identify such indexes so I can then have the same names for all such indexes.
Because the index is the same in both schemas for the same table but have different names. The idea is to standardize the indexes.

example 2

Schemna name: LMN
Index name : I2
Table name : A
columns name : col1, col2

Schema name : PQR
Index name : I9
Table name : A
columns name: col1, col2, col3

Query should return:

Index Name Table Columns
I2 A col1 , col2
I9 A col1 , col2, col3

This means that in different schema's the indexes are actually the same but are based on different number of columns. I need to identfy such indexes and then standardize them in both schemas.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-05 : 09:49:58

Indexes are not "in" a schema. Indexes are created on columns in a table. A table is owned by a schema.

A better naming convention would be to name the indexes after the columns they include, such as col1_col2 instead of I1. That gives you consistent naming and descriptions of which columns the index contains.

ajh
Starting Member

3 Posts

Posted - 2011-05-06 : 05:23:14

i am using this query :

select distinct sk.column_name, sk.index_name
, si.table_schema
from qsys2.syskeys sk , qsys2.syskeys sk1
, qsys2.sysindexes si
where sk.column_name = sk1.column_name
and sk.index_name != sk1.index_name
and si.index_schema = sk.index_schema
and si.table_schema in('ZDBXINV004','ZDBXDEMO4')
and si.table_name = 'TAUDITTRAILDETAIL'
order by 3;

Output I am getting is not what i want.

Sample Data taken from sysindexes and syskeys.

TABLE_NAME || COLUMN_NAME || INDEX_NAME || SCHEMA

T1 || C1 || IDX1 || A
T1 || C1 || IDX2 || B

Output desired is :

TABLE_NAME || COLUMN_NAME

T1 || C1

All other being the same only the index name is different. We conclude by looking at this that in schema B IDX2 is actually IDX1 of schema A.
By identifying such indexes based on similar columns i will replace the name of IDX2 to IDX1. There are many such indexes which have different names.

Kindly guide please.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources