0

This link contains the answer to the question, but it is not completed.

A Sybase DBMS has a notion of catalogs and schemas. So, how do I write a query to retrieve list of indexes for a table inside schema that is inside catalog?

[EDIT]

Consider following scenario:

USE test
GO
CREATE TABLE dbo.test_table(<field_list>)
GO
CREATE TABLE foo.test_table(<field_list>)
GO
CREATE INDEX test_index ON test_table(<index_field_list>)
GO

As you can see there are 2 test_table tables created: one in the schema called dbo and one in the schema called foo. And so now my question would be - how do I write a query that properly check for existence of the index on the table test_table in the schema foo? Because the link I referenced does not differentiate between those 2 tables and therefore will fail in this case. I very much prefer to filter schema and table names rather than using schemaName.tableName format. I hope you get an idea. If not please let me know and I will try to explain with even further details.

[/EDIT]

Igor
  • 5,620
  • 11
  • 51
  • 103
  • Please explain what is not complete? The other question is about Sybase/SAP Adaptive Server Enterprise (ASE). Maybe you would like to ask about Sybase/SAP SQL Anywhere? In ASE there is no such thing as a catalog. There is database, then owner (schema) and object name. – Adam Leszczyński Jul 24 '18 at 10:08
  • Which Sybase (RDBMS) product (ASE? SQLAnywhere? IQ? Advantage?)? If you're using ASE, take a look at the source code for the `sp_helpindex` stored proc for joins between `sysobjects` (tables), `syspartitions` (table/index partitions), `sysindexes` (indexes) and `syscolumns` (columns that make up the index). Other databases will have similar system (aka catalog) tables for deriving information about indexes. – markp-fuso Jul 24 '18 at 12:21
  • @AdamLeszczyński, I'd like to have all indexes for the table that is owned by some specific user (inside the specific schema) which is inside specific catalog (database)? And I do talk about ASE. The concepts are there - it just named differently. I.e., catalog = database and schema = owner. – Igor Jul 24 '18 at 12:31
  • @AdamLeszczyński, unless I can't create a table like this: `USE foo && CREATE TABLE dbo.test(...) && CREATE TABLE bar.test(...)` inside one database? Or can I? Because if I can, those 2 tables can have completely different set of indexes. Or even better - connect to one database with the `sa` user, attach to another with `sa` user in the same server and query table `bar` for indexes. That's why I want to filter the query for catalog and schema. – Igor Jul 24 '18 at 12:36
  • @markp, see my replies above – Igor Jul 24 '18 at 16:47
  • @Igor, can you please give an example schema and example output what you would like to retrieve. Add it to the text of the question. Please mark what is answered by the other answer and what is missing. – Adam Leszczyński Jul 26 '18 at 20:17
  • @AdamLeszczyński, question edited. I hope it is more clear now. – Igor Jul 26 '18 at 20:30

1 Answers1

0

If you're signed into the test database as user foo, the create index command will be applied against the foo.test_table table (precedence is given to objects you own).

If you're signed into the test database as anyone other than foo, and assuming you have permissions to create an index, the create index command will be applied against the dbo.test_table table (precedence goes to objects owner by dbo if you don't own an object of the given name and you have not provided an explicit owner).

If you know you're going to have multiple tables with the same name but different owners, it's a bit more 'clean' to get in a habit of providing explicit owner names (and you're less likely to issue a command against the 'wrong' table).

As for how to check for the existence of an index ... in a nutshell:

  • sysusers contains db user names and ids (name, uid)
  • sysobjects contains object names, object types, object ids and owner ids (name, type, id, uid)
  • sysindexes contains index names, object ids, index ids, and a denormalized list of columns that make up the index (name, id, indid, keys1/keys2)
  • syscolumns contains column names for tables/procs/views, object ids, column ids (name, id, colid)

Sample joins (using old style join clauses):

select ....

from   sysusers   u,
       sysobjects o,
       sysindexes i

where  u.name = '<user_name>'
and    o.name = '<table_name>'
and    o.type = 'T'             -- T=table, P=procedure, V=view
and    i.name = '<index_name>'

and    o.uid  = u.uid
and    o.id   = i.id

The join from sysindexes.keys1/keys2 to syscolumns.colid is a bit convoluted as you need to figure out how you wish to parse the keys1/keys2 columns to obtain individual syscolumns.colid values.

Again, I'd suggest you take a look at the code for the sp_helpindex stored proc as it references all of the appropriate system (aka catalog) tables and includes examples of the necessary join clauses:

exec sybsystemprocs..sp_helptext sp_helpindex,null,null,'showsql'
go
markp-fuso
  • 28,790
  • 4
  • 16
  • 36