--Declare the basename of the table (do not include rpt or qa, this will get filled in below based on which table it is creating)
Declare @Table nvarchar(255)
select @Table = 'mmmReport'

--Create the Report Table

----First Drop an existing table with that name.
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'rpt' + @Table)
begin
	exec('DROP TABLE [dbo].[rpt' + @Table + ']')
	PRINT 'Table rpt' + @Table +' dropped'
end
else
begin
	print 'No table exists with the name rpt' + @Table
end

----second entire report Schema for a new table is below. Remove what is not needed and update datatypes and sizes for what is remaining. 
Declare @CreateRPT nvarchar(max)
select @CreateRPT = 
'CREATE TABLE [dbo].rpt' + @Table + '(
	[AdminId] [int] NOT NULL,
	[Grade] [varchar](2) NOT NULL,
	[RepLevel] [varchar](3) NOT NULL,
	[ProductId] [int] NOT NULL,
	[BurstGroup] [int] NOT NULL,
CONSTRAINT [PK_rptCrystalSchema] PRIMARY KEY CLUSTERED 
(
	[AdminId] ASC,
	[Grade] ASC,
	[RepLevel] ASC,
	[ProductId] ASC,
	[BurstGroup] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]
'
----Third Create the actual table. 
exec (@CreateRPT)

----Fourth Display if the table created or failed. 
if @@ERROR <> 0 
begin
	PRINT 'rpt' + @Table + ' was NOT created due to an Error'
end
else
begin
	PRINT 'rpt' + @Table + ' Created'
end

--Create the QA Table with the same name
----First Drop an existing table with that name.
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'qa' + @Table)
begin
	exec('DROP TABLE [dbo].[qa' + @Table + ']')
	PRINT 'Table qa' + @Table +' dropped'
end
else
begin
	print 'No table exists with the name qa' + @Table
end
----Second Copy the existing table with qa prefix instead of rpt
Declare @CreateQA nvarchar(max)
Select @CreateQA = 
	'SELECT * INTO dbo.qa' + @Table + ' FROM dbo.rpt' + @Table
EXEC (@CreateQA)
----Third Display if the table create or failed. 
if @@ERROR <> 0 
begin
	PRINT 'qa' + @Table + ' was NOT created due to an Error'
end
else
begin
	PRINT 'qa' + @Table + ' Created'
end