/* 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

*/

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