I am using MS sql 7 as database with ASP as front end. in the front end i want to check if a particular table has an index on it. if yes i want to drop index, truncate the table, insert new values into the table and recreate index. this i am doing to make inserting values faster.
in case the front end is being run for first time ever.index would not be present. so if i do drop index in front end script i would get error.
so in this case drop index should not be done and directly table shd be truncated(step 2)
so how can i check if this particular table has an index already created.. any sql query that can send a reply of presence or absence of index in sql server7? any thing related to sysindexes?indid on sysindexes could be used to get this info -
if 0 then no clustered index, but nonclustered indexes may exist
if 255 then clustered index
if >0 and <255 then it is a nonclustered index
So, logic would be something like -
if table has an indid = 0 and no other rows exist in sysindexes for that table then it has no indexes.|||sorry clustered index indid = 1 (not 255 as I said prev)|||From The BOL (The Holy Book) -
Sysindexes table
id -
ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs.
indid -
ID of index:
1 = Clustered index
>1 = Nonclustered
255 = Entry for tables that have text or image data
So you can query the sysindexes table for the desired results
or you can simply use
sp_helpindex your_table_name
No comments:
Post a Comment