Menu
Is there a way to list all SQL Server CE database table indexes, or at least for individual tables?
247
sp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. This is the easiest method to find the indexes in a table. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.
To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.
Discoverying the use of the indexes We need to discover which are the queries using these indexes. In order to do that, we can query the SQL Server Plan Cache, analysing the XML of each query plan and find where the indexes are being used. The DMV sys.
Thank you Arjuna! you pointed me in the right direction..... The following works.
SELECT 'Y' FROM INFORMATION_SCHEMA.INDEXES
where table_name = 'PP_JOB_TICKET_LIVE'
and index_name = 'PP_JOB_TICKET_LIVE_PK'
Thanks so much for your time.
34
-- Retrieves information about the indexes contained in the database. SELECT * FROM INFORMATION_SCHEMA.INDEXES
-- Retrieves all the Tables in the database including the System tables. SELECT * FROM INFORMATION_SCHEMA.TABLES
Arjuna Chiththananda - Retrieving Schema Information of SQL CE Database
187
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
