sql server programming: script to drop indexes -- sql server programming

script to drop indexes

simo
9/29/2003 10:57:31 PM

sql server programming: Hi,
I'm looking for a script or suggestions on how I can go about sql scripting
the dropping of any primary keys (be they constraints or indexes) and/or
clustered indexes for a particular table.
I would like to create a proc and just pass it a table name and will require
this to work on SQL7 & SQL2000. I understand that I should not be using
information schemas instead on querying the systems tables - is this
correct?
Your help and suggestions are much appreciated.

Re: script to drop indexes

Andrew John
9/30/2003 10:33:36 AM

Simo,

There aren't any information schemas on SQL7 if I remember correctly,
so systable access is required.

select si.name as IndexName,
so.name as TableName
from sysindexes si (nolock)
inner join sysobjects so (nolock)
on so.id = si.id
where so.xtype = 'U'
order by so.id

is a start. If the indexes are named by the system then you can use IX and PK searches
to pick out the type. Suggest using a cursor and exec

A safer way is to script the tables (including) the indexes, and manually cut and past

Regards
AJ

[quoted text, click to view]