[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using CTE to Alter Indexes and move to another filegroup.

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

872 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