Using CTE to Alter Indexes and move to another filegroup.

hi,

i'm trying to use the below to move my indexes from one file group to another. However, if there is a composite key, it doens' get created. There are two entries in the result. I'd like to combine the results if there is more than one table name entry with the column names.

hre is the code that i'm trying to work with:


WITH object_cte AS (
SELECT i.name AS PrimaryKey,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
)


SELECT object_cte.TableName, object_cte.PrimaryKey,object_cte.ColumnName
 FROM object_cte
  ORDER BY object_cte.TableName

the results may b like this:

TableName                   PrimaryKey        ColumnName
Table1                       PK_Name                 column1
Table2                        pk_name2               column1
Table2                       pk_name2                column2

I would like to have if the same table name shows up more than once, as here in table2 the tablename listed only once, with both columns like this:

Table2          pk_name2                    column1,column2

then i can generate an alter statement from that.

thanks.
LVL 1
Auerelio VasquezETL DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB Expert/ArchitectCommented:
Hi,
Are you aware of moving Primary Keys - clustered indexes - to different filegroup. It may be heavy operation.

I have edited this script:
http://www.sqlservercentral.com/scripts/Miscellaneous/31893/

Script will create commands for you to create new clustered indexed and drop existing according to this info http://technet.microsoft.com/en-us/library/ms175905%28v=SQL.100%29.aspx


-- Get all existing indexes, but NOT the primary keys
DECLARE CUR CURSOR FOR
SELECT i.name AS PrimaryKey,OBJECT_NAME(ic.OBJECT_ID) AS TableName,COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName, ic.OBJECT_ID AS Tableid
FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

DECLARE @TableID INT
DECLARE @Filegroup VARCHAR(200)
DECLARE @Column_name SYSNAME
DECLARE @Table_name SYSNAME
DECLARE @PK SYSNAME

SET @Filegroup='FG2'

OPEN CUR
FETCH NEXT FROM CUR INTO @PK, @Table_name, @Column_name, @TableID
WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE @IXSQL NVARCHAR(4000) 
   SET @IXSQL = 'CREATE CLUSTERED INDEX '+ @PK + ' ON ' + @Table_name + '('

   -- Get all columns of the index
   DECLARE cIxColumn CURSOR FOR 
      SELECT SC.Name
      FROM Sys.Index_Columns IC
         JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
      WHERE IC.Object_ID = @TableID AND Index_ID = 1
      ORDER BY IC.Index_Column_ID

   DECLARE @IxColumn SYSNAME
   DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1
   
   -- Loop throug all columns of the index and append them to the CREATE statement
   OPEN cIxColumn
   FETCH NEXT FROM cIxColumn INTO @IxColumn
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      IF (@IxFirstColumn = 1)
         SET @IxFirstColumn = 0
      ELSE
         SET @IXSQL = @IXSQL + ', '

      SET @IXSQL = @IXSQL + @IxColumn

      FETCH NEXT FROM cIxColumn INTO @IxColumn
   END
   CLOSE cIxColumn
   DEALLOCATE cIxColumn

   SET @IXSQL = @IXSQL + ') WITH DROP_EXISTING = ON, ON '+ @Filegroup 
   
   -- Print out the CREATE statement for the index
   PRINT @IXSQL

   FETCH NEXT FROM CUR INTO @PK, @Table_name, @Column_name, @TableID
END

CLOSE CUR
DEALLOCATE CUR

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel_PLDB Expert/ArchitectCommented:
Please edit 51'st line to this:
SET @IXSQL = @IXSQL + ') WITH (DROP_EXISTING = ON) ON '+ @Filegroup

Open in new window

When you have spatial indexes or XML it won't work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.