Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    60

    Unanswered: Script to change cluster index to non cluster

    Hi all,

    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.

    Thanks,
    Deepak

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Don't know the direct way, but refer to this Devx article to script the indexes and modify so on.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Here's a link to the SQL Server system tables download:
    http://www.microsoft.com/sql/techinf.../systables.asp

    So, you would do something like this to get all the clustered indexes that includes a CHAR200 col:

    Code:
    SELECT i.name
      FROM sysindexes i,
           sysobjects o,
    	   syscolumns c
     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.
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •