Click here to Skip to main content
14,865,267 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
Is there any script available here which I can import in my sql server which has at least 1000 of tables, records, etc

Thanks,
Sudhir

What I have tried:

I did not try anything . I need a database which has got at least 1000 Tables and records which I cannot create manually. So thought of getting the help
Posted
Updated 15-May-17 23:39pm

This script will create a database called 'The1000' and 1001 tables named T1, T2, T3, ... T1000 containing 1000 identical records each, plus a table called 'track' - the contents of that should be around 2092 rows.
SQL
use master
IF EXISTS(select * from sys.databases where name='The1000')
DROP DATABASE The1000
GO
CREATE DATABASE The1000
GO
use The1000

CREATE TABLE track (id int identity(1,1), dat nvarchar(255))

GO
SET NOCOUNT ON;
	
DECLARE @maxlength int = 30
DECLARE @minlength int = 2
INSERT INTO track
SELECT  
( 
	select top (abs(checksum(newid())) % (@maxlength-@minlength) + @minlength) char(abs(checksum(newid())) % 26 + ascii('A'))  
	from sys.all_objects a1
	where sign(a1.object_id) = sign(t.object_id) /* Meaningless thing to force correlation */
	for xml path('')
) as NewRandomString 
FROM sys.all_objects t;
-- Random text generator from Rob Farley - http://stackoverflow.com/questions/2152204/tsql-pseudo-random-text-generator

DECLARE @start int = 1
DECLARE @end   int = 1000	-- Change this to the number of tables you want
WHILE @start <= @end
BEGIN
	declare @sql nvarchar(max) = 'CREATE TABLE T' + CAST(@start AS NVARCHAR)
	set @sql += '( ID INT, DAT NVARCHAR(255))  '
	set @sql += 'INSERT INTO T' + CAST(@start AS NVARCHAR) + ' SELECT TOP 1000 * FROM track'
	EXEC sp_executeSql @sql
	SET @start += 1
END
If you want random table names then use the dat column from the table track where id = @start instead.
   
You can use SQL Server sample database
Microsoft SQL Server Product Samples: Database - Home[^]
Choose one according to your SQL Server version.

You can generate sample data of your own using CROSS JOIN.
There are some tools available for generating test data too.
SQL Data Generator - Data Generator For MS SQL Server Databases[^]

Hope, it helps :)
   
v2
(updated solution)
-- Create 1000 test tables with 1000 records.
USE [master]
GO

DECLARE @teller1 int;
DECLARE @testname VARCHAR(50);
SET @teller1 = 1;

WHILE @teller1 <= 1000
BEGIN
	SET @testname = 'Test' + STR(@teller1,4);

	BEGIN
		SET @testname = QUOTENAME(@testname);
		EXEC('CREATE DATABASE ' + @testname);
		EXEC('USE ' + @testname + '; CREATE TABLE [Customer](
			[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
			[Name] [nvarchar](128) NULL,
			 CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED 
			(
				[ID] ASC
			)
			) ON [PRIMARY];
			
			DECLARE @teller int;
			SET @teller = 1;
			 
			WHILE @teller <= 1000
			BEGIN
				INSERT INTO [Customer] ([ID],[Name]) VALUES (NEWID(), ''TEST'');
				SET @teller = @teller + 1;
			END;')
	END;

	SET @teller1 = @teller1 + 1;
END

GO
   
v2
Comments
CHill60 16-May-17 4:55am
   
But that's only one table not 1000 tables

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900