Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Posts
    28

    Unanswered: How to get a count of total no of Indexes for a database?

    Hi,

    How can i get a count of total no of Indexes for a database.
    "sp_help tablename" will give u total no of indexes for a particular table...but what if there are 1000 of tables...How to get the total no of indexes for all tables...(excluding system tables)

    I tried this way...but not getting the exact no...
    ***************
    select count(*)
    from sysindexes a, sysobjects b
    where a.id = b.id
    and b.type = 'U'
    and a.name NOT LIKE 'sys%'
    ***************
    Any help wud be appriciated...

    Regards
    Deb

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Re: How to get a count of total no of Indexes for a database?

    Code:
    select count(*) 
    from sysindexes a, sysobjects b
    where a.id = b.id 
    and b.type = 'U' -- Only the suer tables => no system tables (type=S)
    and a.indid > 0  -- remove the tables having no index
    and a.indid < 255 -- remove the text/binary/ADT fields

  3. #3
    Join Date
    May 2002
    Posts
    28

    Re: How to get a count of total no of Indexes for a database?

    Originally posted by fadace
    Code:
    select count(*) 
    from sysindexes a, sysobjects b
    where a.id = b.id 
    and b.type = 'U' -- Only the suer tables => no system tables (type=S)
    and a.indid > 0  -- remove the tables having no index
    and a.indid < 255 -- remove the text/binary/ADT fields
    Thanks for yr reply fadace...
    But that no doesn't match with the exact no of indexes for user defined tables...

  4. #4
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Angry

    I don't belive you.

    Please, provide me the DDL of the user table. I want to see myself.

  5. #5
    Join Date
    May 2002
    Posts
    28
    Originally posted by fadace
    I don't belive you.

    Please, provide me the DDL of the user table. I want to see myself.
    Fadace, i cross checked with the no shown in DBArtisan...actually both are differing...thats why i was confused...yr answer seems to be correct...thanks...

  6. #6
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Originally posted by debkumar
    Fadace, i cross checked with the no shown in DBArtisan...actually both are differing...thats why i was confused...yr answer seems to be correct...thanks...
    Rule 1 : Never believe DBArtisan ;-)

  7. #7
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78
    May be this help with the differences:

    SELECT sum (CASE WHEN indid < 2 and indid < 255 THEN 1 ELSE 0 END) Tables,
    sum (CASE WHEN indid = 0 and indid < 255 THEN 1 ELSE 0 END) Heap_Tables,
    sum (CASE WHEN indid = 1 and indid < 255 THEN 1 ELSE 0 END) Cluster_Tables,
    sum (CASE WHEN indid > 1 and indid < 255 THEN 1 ELSE 0 END) Indexes
    FROM sysindexes,
    sysobjects
    WHERE sysobjects.id = sysindexes.id
    AND sysobjects.type = "U"
    go

    I guess DBArtisan list just the indexes column.
    Bye Bye

    Sebastian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •