Atlanta Custom Software Development 

   Search        Code/Page

User Login



Forgot the Password?
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
» Regular Expr Tester
» Free Tools

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

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

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

   declare @datascript varchar(max)
   set @datascript=''
   set @cnt3=1
   while @cnt3<=@howmanyrows
       set @datascript = @datascript + 'insert into ' + @tblname + ' (' + @insertCols + ') values (' + replace(@insertVals,'{row}',cast(@cnt3 as varchar(20))) + ') ' + char(13) + char(10)
       if (@cnt3 % 100) = 0
           --//Insert every 100 Rows
           set @datascript=''
           print 'create total ' + cast(@cnt3 as varchar(100)) + ' rows'
       set @cnt3=@cnt3+1
   print 'create total ' + cast(@cnt3 as varchar(100)) + ' rows'
   if @createviews=1
       set @sql='create view vw_' + @tblname + @nl + ' as ' + @nl + ' select * from ' + @tblname + @nl    
       print @sql
       execute (@sql)
   if @createprocs=1
       set @sql='create proc usp_' + @tblname + @nl + ' as ' + @nl + ' select * from ' + @tblname + @nl    
       print @sql
       execute (@sql)
   set @cnt1=@cnt1+1

Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with, SQL Server and other MS technologies. He is, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )

Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  BlogsZappySys

© 2008 BinaryWorld LLC. All rights reserved.