Wednesday, December 21, 2011
Tuesday, November 22, 2011
Wednesday, November 16, 2011
Monday, October 24, 2011
sp_helpindex3
CREATE procedure [dbo].[sp_helpindex3]
@objname nvarchar(776) -- the table to check for indexes
as
-- PRELIM
set nocount on
declare @objid int, -- the object id of the table
@indid smallint, -- the index id of an index
@groupid int, -- the filegroup id of an index
@indname sysname,
@groupname sysname,
@status int,
@keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
@inc_columns nvarchar(max),
@inc_Count smallint,
@loop_inc_Count smallint,
@dbname sysname,
@ignore_dup_key bit,
@is_unique bit,
@is_hypothetical bit,
@is_primary_key bit,
@is_unique_key bit,
@auto_created bit,
@no_recompute bit,
@filter_definition nvarchar(max)
-- Check to see that the object names are local to the current database.
select @dbname = parsename(@objname,3)
if @dbname is null
select @dbname = db_name()
else if @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
-- Check to see the the table exists and initialize @objid.
select @objid = object_id(@objname)
if @objid is NULL
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
declare ms_crs_ind cursor local static for
select i.index_id, i.data_space_id, i.name,
i.ignore_dup_key, i.is_unique, i.is_hypothetical, i.is_primary_key, i.is_unique_constraint,
s.auto_created, s.no_recompute, i.filter_definition
from sys.indexes i join sys.stats s
on i.object_id = s.object_id and i.index_id = s.stats_id
where i.object_id = @objid
open ms_crs_ind
fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @filter_definition
-- IF NO INDEX, QUIT
if @@fetch_status < 0
begin
deallocate ms_crs_ind
raiserror(15472,-1,-1,@objname) -- Object does not have any indexes.
return (0)
end
-- create temp tables
CREATE TABLE #spindtab
(
index_name sysname collate database_default NOT NULL,
index_id int,
ignore_dup_key bit,
is_unique bit,
is_hypothetical bit,
is_primary_key bit,
is_unique_key bit,
auto_created bit,
no_recompute bit,
groupname sysname collate database_default NULL,
index_keys nvarchar(2126) collate database_default NOT NULL, -- see @keys above for length descr
filter_definition nvarchar(max),
inc_Count smallint,
inc_columns nvarchar(max)
)
CREATE TABLE #IncludedColumns
( RowNumber smallint,
[Name] nvarchar(128)
)
-- Now check out each index, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
while @@fetch_status >= 0
begin
-- First we'll figure out what the keys are.
declare @i int, @thiskey nvarchar(131) -- 128+3
select @keys = index_col(@objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys + '(-)'
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end
-- Second, we'll figure out what the included columns are.
SELECT @inc_Count = count(*)
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS si
ON (si.index_id > 0
and si.is_hypothetical = 0)
AND (si.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic
ON (ic.column_id > 0
and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0))
AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id)
INNER JOIN sys.columns AS clmns
ON clmns.object_id = ic.object_id
and clmns.column_id = ic.column_id
WHERE ic.is_included_column = 1 and
(si.index_id = @indid) and
(tbl.object_id= @objid)
IF @inc_Count > 0
INSERT #IncludedColumns
SELECT ROW_NUMBER() OVER (ORDER BY clmns.column_id)
, clmns.name
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS si
ON (si.index_id > 0
and si.is_hypothetical = 0)
AND (si.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic
ON (ic.column_id > 0
and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0))
AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id)
INNER JOIN sys.columns AS clmns
ON clmns.object_id = ic.object_id
and clmns.column_id = ic.column_id
WHERE ic.is_included_column = 1 and
(si.index_id = @indid) and
(tbl.object_id= @objid)
SELECT @inc_columns = [Name] FROM #IncludedColumns WHERE RowNumber = 1
SET @loop_inc_Count = 1
WHILE @loop_inc_Count < @inc_Count
BEGIN
SELECT @inc_columns = @inc_columns + ', ' + [Name]
FROM #IncludedColumns WHERE RowNumber = @loop_inc_Count + 1
SET @loop_inc_Count = @loop_inc_Count + 1
END
select @groupname = null
select @groupname = name from sys.data_spaces where data_space_id = @groupid
-- INSERT ROW FOR INDEX
insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical,
@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @groupname, @keys, @filter_definition, @inc_Count, @inc_columns)
-- Next index
fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @filter_definition
end
deallocate ms_crs_ind
-- DISPLAY THE RESULTS
select
'index_name' = index_name,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when index_id = 1 then 'clustered' else 'nonclustered' end
+ case when ignore_dup_key <>0 then ', ignore duplicate keys' else '' end
+ case when is_unique <>0 then ', unique' else '' end
+ case when is_hypothetical <>0 then ', hypothetical' else '' end
+ case when is_primary_key <>0 then ', primary key' else '' end
+ case when is_unique_key <>0 then ', unique key' else '' end
+ case when auto_created <>0 then ', auto create' else '' end
+ case when no_recompute <>0 then ', stats no recompute' else '' end
+ ' located on ' + groupname),
'index_keys' = index_keys,
--'num_included_columns' = inc_Count,
'included_columns' = inc_columns,
'filter_definition' = filter_definition,
'index_id' = index_id
from #spindtab
order by index_name
return (0) -- sp_helpindexwinc2
GO
Thursday, October 20, 2011
Wednesday, October 19, 2011
Friday, October 14, 2011
Thursday, October 06, 2011
Wednesday, October 05, 2011
Monday, October 03, 2011
Thursday, September 22, 2011
Build Hierarchy table from core table
if(object_id('tempdb..#tmp')>1)
drop table #tmp
declare @depth int = 0 , @count int = 0
create table #tmp (parent_id int, child_id int, depth_from_parent int)
SET @depth = 0;
TRUNCATE TABLE #tmp;
INSERT INTO #tmp(parent_id, child_id, depth_from_parent)
VALUES (1, 1, @depth)
WHILE @@ROWCOUNT > 0
BEGIN
SET @depth = @depth + 1
INSERT INTO #tmp(parent_id, child_id, depth_from_parent)
OUTPUT inserted.child_id, inserted.child_id, 0 INTO #tmp
SELECT 1, ou.ou_id, os.depth_from_parent + 1
FROM #tmp os (NOLOCK)
INNER JOIN ou (NOLOCK) ou ON os.child_id = ou.parent_id
WHERE os.depth_from_parent = @depth - 1;
END
SET @depth = 0
WHILE @@ROWCOUNT > 0
BEGIN
SET @depth = @depth + 1;
INSERT INTO #tmp(parent_id, child_id, depth_from_parent)
SELECT os.parent_id, ou.ou_id, @depth
FROM #tmp os
INNER JOIN ou (NOLOCK) ON os.child_id = ou.parent_id --AND os.parent_id <> ou.ou_id -- exclude circular reference
WHERE os.depth_from_parent = @depth - 1 AND os.parent_id <> 1
END
Monday, September 19, 2011
Monday, September 12, 2011
Friday, September 02, 2011
Friday, August 26, 2011
Monday, August 22, 2011
Wednesday, August 03, 2011
minimum account permissions for service broker
USE USERDB
go
CREATE SCHEMA [office\user] AUTHORIZATION [office\user];
GO
CREATE USER [office\user] WITH DEFAULT_SCHEMA = [office\user];
GRANT CREATE PROCEDURE TO [office\user];
GRANT CREATE QUEUE TO [office\user];
GRANT CREATE SERVICE TO [office\user];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [office\user];
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [office\user];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [office\user];
GRANT CONTROL ON SCHEMA::[dbo] TO [office\user];
GRANT IMPERSONATE ON USER::DBO TO [office\user];
GO
go
CREATE SCHEMA [office\user] AUTHORIZATION [office\user];
GO
CREATE USER [office\user] WITH DEFAULT_SCHEMA = [office\user];
GRANT CREATE PROCEDURE TO [office\user];
GRANT CREATE QUEUE TO [office\user];
GRANT CREATE SERVICE TO [office\user];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [office\user];
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [office\user];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [office\user];
GRANT CONTROL ON SCHEMA::[dbo] TO [office\user];
GRANT IMPERSONATE ON USER::DBO TO [office\user];
GO
Friday, July 29, 2011
Thursday, June 30, 2011
Wednesday, June 29, 2011
Tuesday, June 28, 2011
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) ;
;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
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
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
Friday, May 27, 2011
Wednesday, May 25, 2011
Monday, May 23, 2011
Friday, May 20, 2011
indexing - List of all index & index columns in SQL Server DB - Stack Overflow
indexing - List of all index & index columns in SQL Server DB - Stack Overflow
--select * from sys.indexes
--select * From sys.index_columns
select
ind.name, ind.index_id, ic.index_column_id, col.name as indexname, t.name as tablename,
ind.*, ic.*, col.*
from
sys.indexes ind
inner join
sys.index_columns ic on
ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join
sys.columns col on
ic.object_id = col.object_id and ic.column_id = col.column_id
inner join
sys.tables t on
ind.object_id = t.object_id
where
ind.is_primary_key = 0
and ind.is_unique = 0
and ind.is_unique_constraint = 0
and t.is_ms_shipped = 0
and col.name = 'status_id'
and t.name = 'user_lo'
order by
t.name, ind.name, ind.index_id, ic.index_column_id
--select * from sys.indexes
--select * From sys.index_columns
select
ind.name, ind.index_id, ic.index_column_id, col.name as indexname, t.name as tablename,
ind.*, ic.*, col.*
from
sys.indexes ind
inner join
sys.index_columns ic on
ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join
sys.columns col on
ic.object_id = col.object_id and ic.column_id = col.column_id
inner join
sys.tables t on
ind.object_id = t.object_id
where
ind.is_primary_key = 0
and ind.is_unique = 0
and ind.is_unique_constraint = 0
and t.is_ms_shipped = 0
and col.name = 'status_id'
and t.name = 'user_lo'
order by
t.name, ind.name, ind.index_id, ic.index_column_id
Wednesday, May 04, 2011
Friday, April 29, 2011
Tuesday, April 26, 2011
Wednesday, April 13, 2011
Friday, April 08, 2011
Index fragmentation and re-indexing them
SET NOCOUNT ON;
declare @tbl nvarchar(100) = ''
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp
create table #tmp (servername nvarchar(100),dbname nvarchar(100), tblname nvarchar(100), indexid int, indexname nvarchar(1000), avg_fragmentation_in_percent numeric(15,10), string nvarchar(1000))
declare @mid int , @comid int
declare tmp_cur cursor for
select name From sys.tables where type_desc = 'USER_TABLE' and name not like '[__]%'
open tmp_cur
Fetch next from tmp_cur into @tbl
while @@fetch_status = 0
begin
--print @tbl
--dbcc showcontig(@tbl)
insert into #tmp
SELECT
@@servername,db_name(), @tbl, a.index_id, name, avg_fragmentation_in_percent,
(case
when avg_fragmentation_in_percent < 30 and avg_fragmentation_in_percent > 5 then 'ALTER INDEX '+name+' ON '+@tbl+' REORGANIZE ;'
when avg_fragmentation_in_percent > 30 then 'ALTER INDEX '+name+' ON '+@tbl+' REBUILD WITH (ONLINE=ON) ;'
else 'go'
end)
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.'+@tbl+''),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
Fetch next from tmp_cur into @tbl
--print '---'
end
close tmp_cur
deallocate tmp_cur
--select * from #tmp where string != 'go'
----------------------------------------------------
declare @string nvarchar(1000), @avgfrag numeric(20,15), @tblname nvarchar(100), @idx nvarchar(100)
declare tmp_cur cursor for
select string, avg_fragmentation_in_percent,tblname,indexname from #tmp where string != 'go'
open tmp_cur
Fetch next from tmp_cur into @string ,@avgfrag,@tblname,@idx
while @@fetch_status = 0
begin
print '--' +@tblname+' : '+@idx+' : '+convert(nvarchar(20),@avgfrag)
print @string
print 'go'
print 'dbcc dbreindex('''+@tblname+''','''',90)'
print 'go'
print '---'
Fetch next from tmp_cur into @string, @avgfrag,@tblname,@idx
end
close tmp_cur
deallocate tmp_cur
declare @tbl nvarchar(100) = ''
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp
create table #tmp (servername nvarchar(100),dbname nvarchar(100), tblname nvarchar(100), indexid int, indexname nvarchar(1000), avg_fragmentation_in_percent numeric(15,10), string nvarchar(1000))
declare @mid int , @comid int
declare tmp_cur cursor for
select name From sys.tables where type_desc = 'USER_TABLE' and name not like '[__]%'
open tmp_cur
Fetch next from tmp_cur into @tbl
while @@fetch_status = 0
begin
--print @tbl
--dbcc showcontig(@tbl)
insert into #tmp
SELECT
@@servername,db_name(), @tbl, a.index_id, name, avg_fragmentation_in_percent,
(case
when avg_fragmentation_in_percent < 30 and avg_fragmentation_in_percent > 5 then 'ALTER INDEX '+name+' ON '+@tbl+' REORGANIZE ;'
when avg_fragmentation_in_percent > 30 then 'ALTER INDEX '+name+' ON '+@tbl+' REBUILD WITH (ONLINE=ON) ;'
else 'go'
end)
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.'+@tbl+''),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
Fetch next from tmp_cur into @tbl
--print '---'
end
close tmp_cur
deallocate tmp_cur
--select * from #tmp where string != 'go'
----------------------------------------------------
declare @string nvarchar(1000), @avgfrag numeric(20,15), @tblname nvarchar(100), @idx nvarchar(100)
declare tmp_cur cursor for
select string, avg_fragmentation_in_percent,tblname,indexname from #tmp where string != 'go'
open tmp_cur
Fetch next from tmp_cur into @string ,@avgfrag,@tblname,@idx
while @@fetch_status = 0
begin
print '--' +@tblname+' : '+@idx+' : '+convert(nvarchar(20),@avgfrag)
print @string
print 'go'
print 'dbcc dbreindex('''+@tblname+''','''',90)'
print 'go'
print '---'
Fetch next from tmp_cur into @string, @avgfrag,@tblname,@idx
end
close tmp_cur
deallocate tmp_cur
Subscribe to:
Posts (Atom)