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
Solved

Using CTE to Alter Indexes and move to another filegroup.

Posted on 2011-03-16
2
435 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:basile
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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 …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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