SQL Queries - Common SQL Queries - How to retrieve the count of indexes used for a schema

Purpose of Query

The query retrieves the count of Indexes used for a schema

Applicable CAST Version

Release	Yes/No
8.3.x
8.2.x
8.1.x
8.0.x

Applicable RDBMS

RDBMS	Yes/No
Oracle Server
Microsoft SQL Server
CSS2

Query for CSS

Query

SELECT count(i.relname),
  ns.nspname AS schema_name
FROM pg_index AS idx
  JOIN pg_class AS i
    ON i.oid = idx.indexrelid
  JOIN pg_am AS am
    ON i.relam = am.oid
  JOIN pg_namespace AS NS ON i.relnamespace = NS.OID
  JOIN pg_user AS U ON i.relowner = U.usesysid
WHERE NOT nspname LIKE 'pg%' -- Excluding system tables
and ns.nspname = <Schema_name>
group by   ns.nspname

You need to replace <Schema_name> with the name of the Schema

Query result example

--694

Query result interpretation

The query returns the count of used indexes.

Query for Oracle

SELECT count (1)
  FROM USER_INDEXES

Query result example

– 1726

Query result interpretation

The query returns the count of used indexes.

Query for SQL server

SELECT Count (1) 
FROM   information_schema.indexes

Query result example

– 1726

Query result interpretation

The query returns the count of used indexes.

Notes/comments

Related Pages