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

Dariusz Brejnak

Od prawie trzydziestu lat jest pasjonatem informatyki, a zwłaszcza dziedzin dotyczących baz danych, hurtowni danych oraz ogólnie rozumianej tematyki BI. Jego druga pasja to fotografia http://dariuszbrejnak.pl