splitting a comma seperated column into multiple rows

[Hennie7863]

Hi i want to create a table as follows :

if exists (select * from dbo.sysobjects where id =
object_id(N'[Indexes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Indexes]
GO

Create table Indexes(indexname Varchar(100), index_Description
Varchar(100), index_keys Varchar(100))
GO

INSERT INTO Indexes EXEC sp_helpindex 'SDM_Fact_Order_Detail'
GO

This will give me a table (northwind)

IX_Auto_SDM_Fact_FK_Shipped_Date nonclustered located on
SAMIS_SDM_Index FK_Shipped_Date

IX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date clustered located on
SAMIS_SDM_Data1 FK_Insert_Date, FK_Insert_Time

As you see sp_helpindex will give me a comma seperated field. I want
to split the third column FK_Insert_Date, FK_Insert_Time into a extra
row
Like this :

IX_Auto_SDM_Fact_FK_Shipped_Date FK_Shipped_Date
IX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_Date
IX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_Time

Can anyone help me with this?

Thanx

Hennie

[Razvan Socol]

Hi, Hennie

If you need the columns that are part of an index for a specified
table, you can use something like this:

SELECT i.name as IndexName, c.name as ColumnName
FROM sysindexes i
INNER JOIN sysindexkeys k ON i.id=k.id AND i.indid=k.indid
INNER JOIN syscolumns c ON c.id=k.id and c.colid=k.colid
WHERE i.id=OBJECT_ID('Order Details')

If you really want to split some other comma-delimited value (that
cannot be obtained in any other way), see this article by Erland
Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html

Razvan