Unanswered: Script to change cluster index to non cluster
We have many tables which have cluster index on column with datatype 'Char(200)'.
Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
So, you would do something like this to get all the clustered indexes that includes a CHAR200 col:
FROM sysindexes i,
WHERE o.id = i.id
AND o.type = 'U' -- user table
AND indid = 1 -- clustered index
AND o.id = c.id
AND c.type = ??
AND c.length = 200 ?
For c.type look through systypes and find the CHAR, I don't know offhand what the type ID for a char is. And I am not sure if c.length is 200 or what -- take a look in syscolumns and find a couple of the columns you are targeting and see if they have the same data.