Solved

Using CTE to Alter Indexes and move to another filegroup.

Posted on 2011-03-16
2
439 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:Auerelio Vasquez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35155366
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35155444
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question