?
Solved

Using CTE to Alter Indexes and move to another filegroup.

Posted on 2011-03-16
2
Medium Priority
?
442 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 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

752 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