Using CTE to Alter Indexes and move to another filegroup.
Posted on 2011-03-16
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
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.