Today, I’m going to discuss about one of the system view
called “sys.sysindexes”. This contains one row for each index in the
current database.
- To Get All the Index in Current Database
SELECT * FROM sysindexes
- To Get All the Indexes on “Response” table
SELECT * FROM sysindexes where id = object_id('Response')
- To get the row count in “Response” Table
SELECT rowcnt FROM sysindexes where id = object_id('Response') and indid < 2
Answer : ID of the index:
0 = Heap
1 = Clustered index
>1 = Nonclustered index
Even though you are trying
to select the 2 rows (indid < 2 )
, you will never get it. Because your table ('Response')
is either Heap or Clusted/non-clusted index table.
You can use this view to get the row count easily without putting
weight on the table.
Keep in mind :
XML indexes are not supported
Partitioned tables and indexed are not
fully supported
