Open Forum

Before I go building one out on my own, does anybody out there have a SQL script that they run to "clear" all the transaction tables in a database in order to use it for a new company?  We have several companies and are starting a new one that will be similar in setup to the others, we want to basically take one of the existing companies and wipe it preserving the setup tables so we can start fresh.  I've done it before with smaller companies that don't use as many tables, but the one I'm about to clone is a much larger database and has more usage so figured I'd ask before I went to town.  Thanks all!

Joe


------------------------------
Joe Miller
Director of Systems Architecture
Harrison, Walker & Harper
Paris TX
------------------------------

Hi Joe!

I found this link on the internet because that's exactly what I'm working on this morning.   

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
order by TableName;

Another approach:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
order by TableName;

select schema_name(tab.schema_id) + '.' + tab.name as [table],
sum(part.rows) as [rows]
from sys.tables tab
inner join sys.partitions part
on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(part.rows) desc

Once I've got it cleared out, I will back up and restore into TEMPLATEDB so it's available for re-use when this comes up again.

Stay safe!

------------------------------
Gail Jones-Nemeth
Financial Systems Analyst
Creative Associates Int'l
Washington DC
------------------------------

Original Message:
Sent: Jan 23, 2021 10:40 PM
From: Joe Miller
Subject: Copy DB to New Company, Clear Non-Setup Tables

Before I go building one out on my own, does anybody out there have a SQL script that they run to "clear" all the transaction tables in a database in order to use it for a new company?  We have several companies and are starting a new one that will be similar in setup to the others, we want to basically take one of the existing companies and wipe it preserving the setup tables so we can start fresh.  I've done it before with smaller companies that don't use as many tables, but the one I'm about to clone is a much larger database and has more usage so figured I'd ask before I went to town.  Thanks all!

Joe


------------------------------
Joe Miller
Director of Systems Architecture
Harrison, Walker & Harper
Paris TX
------------------------------

So I got impatient over the weekend and started working on it myself.  I have something similar below, but it provides you some columns that you can then utilize to run selects against each table as you review as well as the delete script so that you can copy/past them into a new SQL window.  Doesn't do the whole job, but it helps quite a bit in figuring things out.

SELECT
DB_NAME() as DBName,
SCHEMA_NAME(t.schema_id) SchemaName,
t.[name] TableName,
SUM(p.row_count) TotalRows,
'SELECT ''' + t.[name] + ''' AS TableName, * FROM ' + t.[name] + ' --' + convert(varchar,SUM(p.row_count)) + ' rows' AS SelectSQL,
'--DELETE FROM ' + t.[name] + ' --' + convert(varchar,SUM(p.row_count)) + ' rows' AS DeleteSQL

FROM
sys.tables t
INNER JOIN sys.dm_db_partition_stats p
ON t.object_id = p.object_id
AND t.type_desc = 'USER_TABLE'
AND p.index_id IN (0,1)

--WHERE
-- t.[name] = 'ARTran'

GROUP BY
t.schema_id, t.[name]
HAVING
SUM(p.row_count) > 0
ORDER BY
--TotalRows DESC, t.schema_id, t.[name]
t.[name]

------------------------------
Joe Miller
Director of Systems Architecture
Harrison, Walker & Harper
Paris TX
------------------------------

Original Message:
Sent: Jan 25, 2021 08:46 AM
From: Gail Jones-Nemeth
Subject: Copy DB to New Company, Clear Non-Setup Tables

Hi Joe!

I found this link on the internet because that's exactly what I'm working on this morning.

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
order by TableName;

Another approach:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
order by TableName;

select schema_name(tab.schema_id) + '.' + tab.name as [table],
sum(part.rows) as [rows]
from sys.tables tab
inner join sys.partitions part
on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(part.rows) desc

Once I've got it cleared out, I will back up and restore into TEMPLATEDB so it's available for re-use when this comes up again.

Stay safe!

------------------------------
Gail Jones-Nemeth
Financial Systems Analyst
Creative Associates Int'l
Washington DC

Original Message:
Sent: Jan 23, 2021 10:40 PM
From: Joe Miller
Subject: Copy DB to New Company, Clear Non-Setup Tables

Before I go building one out on my own, does anybody out there have a SQL script that they run to "clear" all the transaction tables in a database in order to use it for a new company?  We have several companies and are starting a new one that will be similar in setup to the others, we want to basically take one of the existing companies and wipe it preserving the setup tables so we can start fresh.  I've done it before with smaller companies that don't use as many tables, but the one I'm about to clone is a much larger database and has more usage so figured I'd ask before I went to town.  Thanks all!

Joe


------------------------------
Joe Miller
Director of Systems Architecture
Harrison, Walker & Harper
Paris TX
------------------------------

I would take the opposite approach and use DB Maintenance to create a new database and then insert rows into the relevant tables using a subset of values from the "template" database; e.g., setup tables should not be just copied as columns like CpnyID and LastBatNbr will have incorrect values.

Project series tables present a mix of problems when "emptying" a database. Several must be populated in an empty database, and some may contain many rows that represent "settings" in a database.

Using DELETE (rather than TRUNCATE) will not reseed identity column ("RecordID") values in tables like ARTran.

------------------------------
Jonathan Van Houtte
Architect
XLstatements, LLC
Clifton Park NY
------------------------------

Original Message:
Sent: Jan 25, 2021 08:54 AM
From: Joe Miller
Subject: Copy DB to New Company, Clear Non-Setup Tables

So I got impatient over the weekend and started working on it myself.  I have something similar below, but it provides you some columns that you can then utilize to run selects against each table as you review as well as the delete script so that you can copy/past them into a new SQL window.  Doesn't do the whole job, but it helps quite a bit in figuring things out.

SELECT
DB_NAME() as DBName,
SCHEMA_NAME(t.schema_id) SchemaName,
t.[name] TableName,
SUM(p.row_count) TotalRows,
'SELECT ''' + t.[name] + ''' AS TableName, * FROM ' + t.[name] + ' --' + convert(varchar,SUM(p.row_count)) + ' rows' AS SelectSQL,
'--DELETE FROM ' + t.[name] + ' --' + convert(varchar,SUM(p.row_count)) + ' rows' AS DeleteSQL

FROM
sys.tables t
INNER JOIN sys.dm_db_partition_stats p
ON t.object_id = p.object_id
AND t.type_desc = 'USER_TABLE'
AND p.index_id IN (0,1)

--WHERE
-- t.[name] = 'ARTran'

GROUP BY
t.schema_id, t.[name]
HAVING
SUM(p.row_count) > 0
ORDER BY
--TotalRows DESC, t.schema_id, t.[name]
t.[name]

------------------------------
Joe Miller
Director of Systems Architecture
Harrison, Walker & Harper
Paris TX

Original Message:
Sent: Jan 25, 2021 08:46 AM
From: Gail Jones-Nemeth
Subject: Copy DB to New Company, Clear Non-Setup Tables

Hi Joe!

I found this link on the internet because that's exactly what I'm working on this morning.

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
order by TableName;

Another approach:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
order by TableName;

select schema_name(tab.schema_id) + '.' + tab.name as [table],
sum(part.rows) as [rows]
from sys.tables tab
inner join sys.partitions part
on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(part.rows) desc

Once I've got it cleared out, I will back up and restore into TEMPLATEDB so it's available for re-use when this comes up again.

Stay safe!

------------------------------
Gail Jones-Nemeth
Financial Systems Analyst
Creative Associates Int'l
Washington DC

Original Message:
Sent: Jan 23, 2021 10:40 PM
From: Joe Miller
Subject: Copy DB to New Company, Clear Non-Setup Tables

Before I go building one out on my own, does anybody out there have a SQL script that they run to "clear" all the transaction tables in a database in order to use it for a new company?  We have several companies and are starting a new one that will be similar in setup to the others, we want to basically take one of the existing companies and wipe it preserving the setup tables so we can start fresh.  I've done it before with smaller companies that don't use as many tables, but the one I'm about to clone is a much larger database and has more usage so figured I'd ask before I went to town.  Thanks all!

Joe


------------------------------
Joe Miller
Director of Systems Architecture
Harrison, Walker & Harper
Paris TX
------------------------------

Original Message:
Sent: 1/25/2021 9:26:00 AM
From: Jonathan Van Houtte
Subject: RE: Copy DB to New Company, Clear Non-Setup Tables

I would take the opposite approach and use DB Maintenance to create a new database and then insert rows into the relevant tables using a subset of values from the "template" database; e.g., setup tables should not be just copied as columns like CpnyID and LastBatNbr will have incorrect values.

Project series tables present a mix of problems when "emptying" a database. Several must be populated in an empty database, and some may contain many rows that represent "settings" in a database.

Using DELETE (rather than TRUNCATE) will not reseed identity column ("RecordID") values in tables like ARTran.

------------------------------
Jonathan Van Houtte
Architect
XLstatements, LLC
Clifton Park NY
------------------------------

Original Message:
Sent: Jan 25, 2021 08:54 AM
From: Joe Miller
Subject: Copy DB to New Company, Clear Non-Setup Tables

So I got impatient over the weekend and started working on it myself.  I have something similar below, but it provides you some columns that you can then utilize to run selects against each table as you review as well as the delete script so that you can copy/past them into a new SQL window.  Doesn't do the whole job, but it helps quite a bit in figuring things out.

SELECT
DB_NAME() as DBName,
SCHEMA_NAME(t.schema_id) SchemaName,
t.[name] TableName,
SUM(p.row_count) TotalRows,
'SELECT ''' + t.[name] + ''' AS TableName, * FROM ' + t.[name] + ' --' + convert(varchar,SUM(p.row_count)) + ' rows' AS SelectSQL,
'--DELETE FROM ' + t.[name] + ' --' + convert(varchar,SUM(p.row_count)) + ' rows' AS DeleteSQL

FROM
sys.tables t
INNER JOIN sys.dm_db_partition_stats p
ON t.object_id = p.object_id
AND t.type_desc = 'USER_TABLE'
AND p.index_id IN (0,1)

--WHERE
-- t.[name] = 'ARTran'

GROUP BY
t.schema_id, t.[name]
HAVING
SUM(p.row_count) > 0
ORDER BY
--TotalRows DESC, t.schema_id, t.[name]
t.[name]

------------------------------
Joe Miller
Director of Systems Architecture
Harrison, Walker & Harper
Paris TX

Original Message:
Sent: Jan 25, 2021 08:46 AM
From: Gail Jones-Nemeth
Subject: Copy DB to New Company, Clear Non-Setup Tables

Hi Joe!

I found this link on the internet because that's exactly what I'm working on this morning.

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
order by TableName;

Another approach:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
order by TableName;

select schema_name(tab.schema_id) + '.' + tab.name as [table],
sum(part.rows) as [rows]
from sys.tables tab
inner join sys.partitions part
on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(part.rows) desc

Once I've got it cleared out, I will back up and restore into TEMPLATEDB so it's available for re-use when this comes up again.

Stay safe!

------------------------------
Gail Jones-Nemeth
Financial Systems Analyst
Creative Associates Int'l
Washington DC

Original Message:
Sent: Jan 23, 2021 10:40 PM
From: Joe Miller
Subject: Copy DB to New Company, Clear Non-Setup Tables

Before I go building one out on my own, does anybody out there have a SQL script that they run to "clear" all the transaction tables in a database in order to use it for a new company?  We have several companies and are starting a new one that will be similar in setup to the others, we want to basically take one of the existing companies and wipe it preserving the setup tables so we can start fresh.  I've done it before with smaller companies that don't use as many tables, but the one I'm about to clone is a much larger database and has more usage so figured I'd ask before I went to town.  Thanks all!

Joe


------------------------------
Joe Miller
Director of Systems Architecture
Harrison, Walker & Harper
Paris TX
------------------------------

Hi everyone, is there a way to differentiate the catalogs from the transaction tables? this is because I need to leave the catalog screens intact I hope you can help me, thanks for the excellent work

------------------------------
Manuel Vela
Tec
------------------------------

Original Message:
Sent: Jan 25, 2021 08:46 AM
From: Gail Jones-Nemeth
Subject: Copy DB to New Company, Clear Non-Setup Tables

Hi Joe!

I found this link on the internet because that's exactly what I'm working on this morning.

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
order by TableName;

Another approach:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) and i.rows > 0
order by TableName;

select schema_name(tab.schema_id) + '.' + tab.name as [table],
sum(part.rows) as [rows]
from sys.tables tab
inner join sys.partitions part
on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(part.rows) desc

Once I've got it cleared out, I will back up and restore into TEMPLATEDB so it's available for re-use when this comes up again.

Stay safe!

------------------------------
Gail Jones-Nemeth
Financial Systems Analyst
Creative Associates Int'l
Washington DC

Original Message:
Sent: Jan 23, 2021 10:40 PM
From: Joe Miller
Subject: Copy DB to New Company, Clear Non-Setup Tables

Before I go building one out on my own, does anybody out there have a SQL script that they run to "clear" all the transaction tables in a database in order to use it for a new company?  We have several companies and are starting a new one that will be similar in setup to the others, we want to basically take one of the existing companies and wipe it preserving the setup tables so we can start fresh.  I've done it before with smaller companies that don't use as many tables, but the one I'm about to clone is a much larger database and has more usage so figured I'd ask before I went to town.  Thanks all!

Joe


------------------------------
Joe Miller
Director of Systems Architecture
Harrison, Walker & Harper
Paris TX
------------------------------

Original Message:
Sent: 1/23/2021 10:40:00 PM
From: Joe Miller
Subject: Copy DB to New Company, Clear Non-Setup Tables

Before I go building one out on my own, does anybody out there have a SQL script that they run to "clear" all the transaction tables in a database in order to use it for a new company?  We have several companies and are starting a new one that will be similar in setup to the others, we want to basically take one of the existing companies and wipe it preserving the setup tables so we can start fresh.  I've done it before with smaller companies that don't use as many tables, but the one I'm about to clone is a much larger database and has more usage so figured I'd ask before I went to town.  Thanks all!

Joe


------------------------------
Joe Miller
Director of Systems Architecture
Harrison, Walker & Harper
Paris TX
------------------------------