Wednesday, December 21, 2011

SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL « SQL Server Journey with SQLAuthority

SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL « SQL Server Journey with SQLAuthority

How do I get a list of SQL Server tables and their row counts?

How do I get a list of SQL Server tables and their row counts?

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

Standardize SQL Server Installations with Configuration Files

Standardize SQL Server Installations with Configuration Files

What does my SQL Server data look like over the wire?

What does my SQL Server data look like over the wire?

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

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

Monday, June 27, 2011

Parse string characters and separate them

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

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

Parsing Chess FEN string into Board setting

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

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

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

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

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

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


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

Thursday, June 02, 2011

Collect every index from DB to compare

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

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

open tmp_cur
Fetch next from tmp_cur into @tablename

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

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

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

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

close tmp_cur
deallocate tmp_cur

drop table __tmp_all_indexes

select *
into __tmp_all_indexes
from #tmp2

Friday, May 20, 2011

Index - sp_helpindex2 to show include columns

Kimberly L. Tripp | sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex

Determine SQL Server memory use by database and object

Determine SQL Server memory use by database and object

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

Friday, April 08, 2011

SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table « Journey to SQLAuthority

SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table « Journey to SQLAuthority

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

Microsoft SQL Server 2000 Index Defragmentation Best Practices

Microsoft SQL Server 2000 Index Defragmentation Best Practices

Reorganizing and Rebuilding Indexes

Reorganizing and Rebuilding Indexes