Thursday, June 30, 2011

Procedure Execution Statistics

SQL Server 2005 Stored Procedure Execution Statistics | Glenn Berry's SQL Server Performance

Monitoring Stored Procedure Usage Count

Monitoring Stored Procedure Usage — DatabaseJournal.com

Monday, June 27, 2011

Parse string characters and separate them

DECLARE @String NVARCHAR(99) = N'abcdefghijk' ;

;WITH Numbers
AS ( SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < 99
)
SELECT SUBSTRING(@String, Number, 1) AS SingleChar
FROM Numbers
WHERE Number <= LEN(@String) ;

Parsing Chess FEN string into Board setting

IF OBJECT_ID('TC58','U') IS NOT NULL BEGIN
DROP TABLE TC58
END
GO

CREATE TABLE TC58(
ID INT IDENTITY PRIMARY KEY,
Layout VARCHAR(MAX)
)
GO

--INSERT INTO TC58(Layout) SELECT 'rnbqk2r/ppppbppp/5n2/4p3/2B1P3/5Q2/PPPP1PPP/RNB1K1NR'
--INSERT INTO TC58(Layout) SELECT 'rnbq3r/pppp2pp/8/2ppp3/2B1K3/5P2/PPP2PPP/RNB3NR'
INSERT INTO TC58(Layout) SELECT '4r3/1k6/pN1p4/3P1p1p/3B4/3K1n2/PPP4R/8'-- w - - 7 27
--SELECT * FROM TC58

/*
select
0,
SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ) ,
CHARINDEX('/',Layout,0) , --8
SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) ,
CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) , --17
SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ) ,
CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) , --21
SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1) ,
CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1), --25
SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ) ,
CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) , --31
SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ) ,
CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) , --35
SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ),
CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) , --44
SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1))
from TC58
*/

/*
select ID, 8 as RowID , SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ) from TC58
union all
select ID, 7 as RowID ,SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) from TC58
union all
select ID, 6 as RowID ,SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ) from TC58
union all
select ID, 5 as RowID ,SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1) from TC58
union all
select ID, 4 as RowID ,SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ) from TC58
union all
select ID, 3 as RowID ,SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ) from TC58
union all
select ID, 2 as RowID ,SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ) from TC58
union all
select ID, 1 as RowID ,SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1)) from TC58
*/

/*
select ID, 8 as RowID ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)) from TC58
union all
select ID, 7 as RowID ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) ,'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)) from TC58
union all
select ID, 6 as RowID ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)) from TC58
union all
select ID, 5 as RowID ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)) from TC58
union all
select ID, 4 as RowID ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)) from TC58
union all
select ID, 3 as RowID ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)) from TC58
union all
select ID, 2 as RowID ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)) from TC58
union all
select ID, 1 as RowID ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1)),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)) from TC58
*/


select ID, 8 as RowID
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),1,1) as 'a'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),2,1) as 'b'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),3,1) as 'c'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),4,1) as 'd'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),5,1) as 'e'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),6,1) as 'f'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),7,1) as 'g'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, 0 ,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)) ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),8,1) as 'h'
from TC58
union all
select ID, 7 as RowID
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) ,'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),1,1) as 'a'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) ,'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),2,1) as 'b'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) ,'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),3,1) as 'c'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) ,'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),4,1) as 'd'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) ,'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),5,1) as 'e'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) ,'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),6,1) as 'f'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) ,'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),7,1) as 'g'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,0)+1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,0) - CHARINDEX('/',Layout,0)) ) ,'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),8,1) as 'h'
from TC58
union all
select ID, 6 as RowID
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),1,1) as 'a'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),2,1) as 'b'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),3,1) as 'c'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),4,1) as 'd'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),5,1) as 'e'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),6,1) as 'f'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),7,1) as 'g'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+ 1, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),8,1) as 'h'
from TC58
union all
select ID, 5 as RowID
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),1,1) as 'a'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),2,1) as 'b'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),3,1) as 'c'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),4,1) as 'd'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),5,1) as 'e'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),6,1) as 'f'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),7,1) as 'g'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)- CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1) -1),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),8,1) as 'h'
from TC58
union all
select ID, 4 as RowID
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),1,1) as 'a'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),2,1) as 'b'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),3,1) as 'c'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),4,1) as 'd'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),5,1) as 'e'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),6,1) as 'f'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),7,1) as 'g'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),8,1) as 'h'
from TC58
union all
select ID, 3 as RowID
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),1,1) as 'a'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),2,1) as 'b'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),3,1) as 'c'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),4,1) as 'd'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),5,1) as 'e'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),6,1) as 'f'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),7,1) as 'g'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) +1,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1) - 1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),8,1) as 'h'
from TC58
union all
select ID, 2 as RowID
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),1,1) as 'a'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),2,1) as 'b'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),3,1) as 'c'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),4,1) as 'd'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),5,1) as 'e'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),6,1) as 'f'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),7,1) as 'g'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) + 1 , CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1) -1 ),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),8,1) as 'h'
from TC58
union all
select ID, 1 as RowID
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1)),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),1,1) as 'a'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1)),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),2,1) as 'b'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1)),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),3,1) as 'c'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1)),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),4,1) as 'd'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1)),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),5,1) as 'e'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1)),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),6,1) as 'f'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1)),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),7,1) as 'g'
,substring(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING ( Layout, CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1) +1 ,LEN(layout) - CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,CHARINDEX('/',Layout,0)+1)+1)+1)+1)+1)+1)),'1',REPLICATE(' ',1)),'2',REPLICATE(' ',2)), '3',REPLICATE(' ',3)), '4',REPLICATE(' ',4)), '5',REPLICATE(' ',5)), '6',REPLICATE(' ',6)), '7',REPLICATE(' ',7)), '8',REPLICATE(' ',8)),8,1) as 'h'
from TC58

Thursday, June 02, 2011

Collect every index from DB to compare

if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp
create table #tmp (index_name nvarchar(256), index_description nvarchar(500), index_keys nvarchar(500), included_columns nvarchar(1000),filter_definition nvarchar(256))
if(OBJECT_ID('tempdb..#tmp2')>1)
drop table #tmp2
create table #tmp2 (dbname nvarchar(256), tablename nvarchar(256), index_name nvarchar(256), index_description nvarchar(500), index_keys nvarchar(500), included_columns nvarchar(1000),filter_definition nvarchar(256))

declare @tablename nvarchar(256), @sql nvarchar(4000)
declare tmp_cur cursor for
select name from sys.tables where schema_id = 1

open tmp_cur
Fetch next from tmp_cur into @tablename

while @@fetch_status = 0
begin
print @tablename
set @sql = 'insert into #tmp (index_name , index_description , index_keys , included_columns )
exec sp_helpindex2 '''+@tablename+'''

insert into #tmp2 (dbname , tablename, index_name , index_description , index_keys , included_columns )
select db_name(), '''+@tablename+''' , index_name , index_description , index_keys , included_columns from #tmp

truncate table #tmp
'
print @sql
exec sp_executesql @sql

Fetch next from tmp_cur into @tablename
print '---'
end

close tmp_cur
deallocate tmp_cur

drop table __tmp_all_indexes

select *
into __tmp_all_indexes
from #tmp2