Script for Generate Sample Data for Table Name

create proc GENERATEQUERYSampleData (@TBL varchar(40))
as
begin
declare @para varchar(max)= 'DECLARE @count INT
SET @count = 1
SET NOCOUNT ON ;

WHILE @count <= 100000
BEGIN

INSERT INTO ';

select @para +=TABLE_SCHEMA+'.'+TABLE_NAME +'('
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME=@TBL

select @para += cl.COLUMN_NAME+','
from INFORMATION_SCHEMA.COLUMNS cl
join sys.columns C
    on c.is_identity=0 and Cl.TABLE_NAME=OBJECT_NAME(object_id) and cl.COLUMN_NAME=c.name
where cl.TABLE_NAME= @TBL;

SET @para = LEFT(@para,LEN(@para)-1);
SET @para += ')' +char(10)+' select ';


select @para += case
        when cl.DATA_TYPE in ('bit')
        then ' DATEPART(MILLISECOND, GETDATE()) >= 500 THEN 0 ELSE 1 END '
        when cl.DATA_TYPE in ('bigint','smallint','tinyint','int','numeric')
        then '(ABS(CHECKSUM(NEWID())) % 1231) + ((ABS(CHECKSUM(NEWID())) % 634) * 1)'
        when cl.DATA_TYPE in ('decimal','float','money')
        then '(ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) '
        when cl.DATA_TYPE ='date'
        then 'dateadd(YEAR, (ABS(CHECKSUM(NEWID())) % 1000),dateadd(DAY, (ABS(CHECKSUM(NEWID())) % 10) , dateadd(MONTH, (ABS(CHECKSUM(NEWID())) % 10) , cast (GETDATE() as date ))))'
        when cl.data_type ='datetime'
        then 'DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1, DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE()))'
        when cl.Data_type in ('char','nchar','nvarchar','varchar','binary','varbinary')
        then dbo.GetString(CHARACTER_MAXIMUM_LENGTH)
        end +','
from INFORMATION_SCHEMA.COLUMNS cl
join sys.columns C
    on c.is_identity=0 and Cl.TABLE_NAME=OBJECT_NAME(object_id) and cl.COLUMN_NAME=c.name
where cl.TABLE_NAME= @TBL;

SET @para = LEFT(@para,LEN(@para)-1)
SET @para += char(10)+char(10)+'SET @count += 1
END'

print  @para

end


--------------------------

CREATE function GetString (@Type varchar(20))
returns varchar(500)
as
begin


declare @q varchar(500);
set @Type =lTrim(rtrim(@Type))
if(@Type =1)
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) '

end
else if(@Type =2)
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)'

end
else if(@Type =3)
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) '

end
else if(@Type =4)
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)'

end
else if(@Type =5)
begin

set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+ CHAR((ABS(CHECKSUM(NEWID())) % 26)+ 97) '

end
else
begin


set @q= 'CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)+ CHAR((ABS(CHECKSUM(NEWID())) % 26)+ 97)  +CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97)'

end
return @q
end