Binaryworld - Script to create dummy tables/procs and views for testing purpose ... [ SQL Server -> T-SQL ]

This script can generate N number of Tables/Views and Stpored Procs in few seconds with some dummy data. I use this for stress testing and create dummy tables with data.

Change @howmanytables to create N number of tables and then set
- @howmanycolumns to create N number of dummy columns per table with different datatypes
- @howmanyrows to create N number of dummy rows per table

Also set @createprocs and @createviews to 1 if you want to create dummy views and procs for each table

Click here to copy the following block

set nocount on

declare @howmanytables int
declare @howmanycolumns int
declare @howmanyrows int

set @howmanytables=1 -- Total tables to generate
set @howmanycolumns=350 -- Total columns per table to generate
set @howmanyrows = 500 -- Total rows per table
-----------------------------

declare @createprocs bit -- create stored proc for each table
declare @createviews bit -- create view for each table

set @createprocs=0
set @createviews=0
-------------------------------
declare @cnt1 int
declare @cnt2 int
declare @cnt3 int

declare @tblname varchar(100)

declare @cols varchar(max)
declare @collist varchar(max)
declare @valuelist varchar(max)
declare @nl char(2)

set @nl=char(13) + char(10)

declare @colname varchar(100)
declare @sql varchar(max)

set @cnt1=1

--//Tables-----------------------------
while @cnt1<=@howmanytables
begin
set @cols=''
set @cnt2=1
declare @insertCols varchar(max)
declare @insertVals varchar(max)
select @insertCols='',@insertVals=''
--//Columns-----------------------------
while @cnt2<=@howmanycolumns
begin
set @colname='col_' + cast(@cnt2 as varchar(10))
if @cnt2=1
begin
set @insertCols=@colname
set @insertVals='''{row}'''
set @cols=@colname + ' int not null primary key'
end
else
begin
set @insertCols=@insertCols + ',' + @colname
set @insertVals=@insertVals + ',''R{row}-' + @colname + ''''
set @cols=@cols + ',' + @colname + ' varchar(100) null'
end
set @cnt2=@cnt2+1
end

set @tblname= 'table_' + cast(@cnt1 as varchar(10))
set @sql='create table ' + @tblname + '(' + @cols + ')'+ @nl
print @sql
execute (@sql)

--//Rows-----------------------------
declare @datascript varchar(max)
set @datascript=''
set @cnt3=1
while @cnt3<=@howmanyrows
begin
set @datascript = @datascript + 'insert into ' + @tblname + ' (' + @insertCols + ') values (' + replace(@insertVals,'{row}',cast(@cnt3 as varchar(20))) + ') ' + char(13) + char(10)
if (@cnt3 % 100) = 0
begin
--//Insert every 100 Rows
exec(@datascript)
set @datascript=''
print 'create total ' + cast(@cnt3 as varchar(100)) + ' rows'
end
set @cnt3=@cnt3+1
end
print 'create total ' + cast(@cnt3 as varchar(100)) + ' rows'
exec(@datascript)

--//views-----------------------------
if @createviews=1
begin
set @sql='create view vw_' + @tblname + @nl + ' as ' + @nl + ' select * from ' + @tblname + @nl
print @sql
execute (@sql)
end

--//procs-----------------------------
if @createprocs=1
begin
set @sql='create proc usp_' + @tblname + @nl + ' as ' + @nl + ' select * from ' + @tblname + @nl
print @sql
execute (@sql)
end
set @cnt1=@cnt1+1
end