W poście dotyczącym maksymalnej ilości kolumn w indeksach i statystykach ciężko by było ręcznie wpisać kod np. statystyki z 64 testowymi kolumnami. Analogicznie ma się sprawa z indeksami czy też samą tabelą. Stworzyłem skrypty do generowania tych obiektów, które przedstawiam poniżej.
Generowanie tabeli: podajemy parametry wymagane @TableName i @NumofCols oraz opcjonalne @ColPrefixName i @ColTypes.
-----------------------------------
-- CreateMaxColTable.sql
-- Dariusz Brejnak
-----------------------------------
USE TestDB
go
-- exec [dbo].[CreateMaxColTable] @tablename =N'Test3',@NumofCols=64, @ColTypes=N'VARCHAR(10)'
CREATE or ALTER Proc [dbo].[CreateMaxColTable]
(@TableName nvarchar(100)
,@NumofCols int
,@ColPrefixName nvarchar(20) = N'Col'
,@ColTypes nvarchar(20) = N'INT'
)
AS
BEGIN
DECLARE @i INT = 1,
@SQL VARCHAR(MAX),
@x INT = 1,
@zero varchar(4) ='',
@colsufix varchar(4)
SELECT @x = case
when @NumofCols < 10 THEN 1
when @NumofCols < 100 THEN 2
when @NumofCols < 1000 THEN 3
Else 4
end
SET @zero=replicate('0',@x)
SET @SQL='CREATE TABLE ' + @TableName + char(13)+char(10)+'('+char(13)+char(10)
WHILE @i<=@NumofCols
BEGIN
SELECT @colsufix = right(@zero+cast(@i as varchar),@x)
SELECT @SQL= @SQL+'['+@ColPrefixName+@colsufix +'] '+@ColTypes+' , '+IIF(@i%5=0,char(13)+char(10),'')
SET @i = @i + 1
END
SELECT @SQL = substring(@SQL,0,IIF((@i-1)%5=0,len(@sql)-2,len(@SQL))-1)
SELECT @SQL= @SQL+ char(13)+char(10)+' )'
PRINT @sql
--exec (@SQL)
END
Kolejny skrypt służy wygenerowaniu kodu tworzącego indeksy. Podajemy parametry wymagane @TableName, @IndexName i @NumofCols oraz opcjonalne @ColPrefixName.
-----------------------------------
-- CreateMaxIndexOnTable.sql
-- Dariusz Brejnak
-----------------------------------
USE TestDB
Go
-- exec [dbo].[CreateMaxIndexOnTable] @tablename =N'Test3',@NumofCols=100, @IndexName =N'IX_Test3'
CREATE or ALTER PROC [dbo].[CreateMaxIndexOnTable]
(@TableName nvarchar(100)
,@IndexName nvarchar(100)
,@NumofCols int
,@ColPrefixName nvarchar(20) = N'Col'
)
AS
BEGIN
DECLARE @i INT = 1,
@SQL VARCHAR(MAX),
@x INT = 1,
@zero varchar(4) ='',
@colsufix varchar(4)
SELECT @x = case
when @NumofCols < 10 THEN 1
when @NumofCols < 100 THEN 2
when @NumofCols < 1000 THEN 3
Else 4
end
SET @zero=replicate('0',@x)
SET @SQL='CREATE INDEX '+@IndexName+ ' ON ' + @TableName + char(13)+char(10)+'('+char(13)+char(10)
WHILE @i<=@NumofCols
BEGIN
SELECT @colsufix = right(@zero+cast(@i as varchar),@x)
SELECT @SQL= @SQL+'['+@ColPrefixName+@colsufix +'], '+IIF(@i%5=0,char(13)+char(10),'')
SET @i = @i + 1
END
SELECT @SQL = substring(@SQL,0,IIF((@i-1)%5=0,len(@sql)-2,len(@SQL))-1)
SELECT @SQL= @SQL+ char(13)+char(10)+' )'
PRINT @sql
--exec (@SQL)
END
Ostatni skrypt służy do wygenerowania kodu tworzącego statystyki. Podajemy parametry wymagane @TableName, @StatName i @NumofCols oraz opcjonalne @ColPrefixName.
-----------------------------------
-- CreateMaxStatOnTable.sql
-- Dariusz Brejnak
-----------------------------------
USE TestDB
Go
-- exec [dbo].[CreateMaxStatOnTable] @tablename =N'Test4',@NumofCols=65, @StatName =N'Stat4'
CREATE or ALTER PROC [dbo].[CreateMaxStatOnTable]
(@TableName nvarchar(100)
,@StatName nvarchar(100)
,@NumofCols int
,@ColPrefixName nvarchar(20) = N'Col'
)
AS
BEGIN
DECLARE @i INT = 1,
@SQL VARCHAR(MAX),
@x INT = 1,
@zero varchar(4) ='',
@colsufix varchar(4)
SELECT @x = case
when @NumofCols < 10 THEN 1
when @NumofCols < 100 THEN 2
when @NumofCols < 1000 THEN 3
Else 4
end
SET @zero=replicate('0',@x)
SET @SQL='CREATE STATISTICS '+@StatName+ ' ON ' + @TableName + char(13)+char(10)+'('+char(13)+char(10)
WHILE @i<=@NumofCols
BEGIN
SELECT @colsufix = right(@zero+cast(@i as varchar),@x)
SELECT @SQL= @SQL+'['+@ColPrefixName+@colsufix+'], '+IIF(@i%5=0,char(13)+char(10),'')
SET @i = @i + 1
END
SELECT @SQL = substring(@SQL,0,IIF((@i-1)%5=0,len(@sql)-3,len(@SQL)))
SELECT @SQL= @SQL+ char(13)+char(10)+' )'
PRINT @sql
--exec (@SQL)
END
