Question:
I'm finished my test or pilot for my company and I do not want to transfer the data to my live install... how do I delete all the data in the tables but keep my users and companies still registered.
Or
I had to perform my test or pilot for my company in my LIVE database. How do I delete all the data so users can start from scratch?
Answer:
Note: We did not provide a delete data option in our Upgrader as it is too dangerous of a task if someone were to run it accidentally on the wrong company.
NEVER run these processes on live data that you want to keep!
ALWAYS make a backup before starting this process!
READ the important notices at the end of this article!
There are two ways to clear your database of data. The first way is less technical but takes the user a bit longer to execute. The second takes less time to execute but is a bit harder for the user who does not know Sql queries very well.
Delete Records Using SQL Management Studio Graphical Interface
Open SQL Management Studio and select all tables that start with HQMS or HZS in the object explorer details pane EXCEPT for the HQMS_Setup table (see note at end of article). Then press the Delete key. You will get errors but keep deleting until all the errors are gone and it says that all tables have been deleted.
Open the Upgrader application and execute the Install/Upgrade process on the sql database which will recreate all the HQMS tables that were removed as well as put the system generated HQMS_SysCodes records back in the system. After running the upgrader you will have to enable your importing of data if you are integrated with an ERP system.
Delete Records Using SQL queries:
Run one of the following query in SSMS:
1) select 'delete from ' + name from sys.tables where (name like 'HZS%' OR name like 'HQMS_%') and name not like 'HQMS_Setup'
You may want to also include the HQMS_Syscodes table in the query above depending on your needs
2) select 'delete from ' + name from sys.tables where (name like 'HZS%' OR name like 'HQMS_%')
The difference between these queries is just the HQMS_Setup table. (see note at the end of this article)
3) The following query will allow you to selectivity delete different types of table data depending on what stage of implementation you are at:
SELECT objtype, objname, name, value, 'delete from ' + objname as DeleteStatement
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL)
WHERE name = 'Type' and objname like 'HQMS_%' and
(value = 'TRX' OR
value = 'OPR' OR
value = 'SETUP' OR
value = 'MSTR')
order by objname
Audit Tables Disabling
When you delete a large amount of data the Audit triggers on the HQMS SQL tables will execute which could take the script longer to run. You can disable and enable these trigger by running the following:
select CHAR(13) + 'GO' + CHAR(10) +' DISABLE TRIGGER ALL ON ' + name from sys.tables where (name like 'HZS%' OR name like 'HQMS_%') and name not like 'HQMS_Setup'
You have to run the query with results to text turned on due to a bug in SQL. Then select the text, copy and paste it into a new query window and run it.
And then when the delete queries are finished you can run this:
select CHAR(10) + 'GO ' + CHAR(10) +' ENABLE TRIGGER ALL ON ' + name from sys.tables where (name like 'HZS%' OR name like 'HQMS_%') and name not like 'HQMS_Setup'
BEFORE running these queries please make sure you don't have any tables that just happen to have HZS or HQMS at the beginning of them that are not related to our Quality System.
Highlight the single column of data that is returned and Ctrl-C to copy. Open a new query window and paste (Ctrl-V) the results into that query. Execute the query until it says all completed without errors. Please note that executing this query only once will only delete the top level tables data in QES. Due to referential integrity you have to keep running the script until all tables show 0 records.
YOU MUST RUN the Upgrader application and execute the Install/Upgrade process on the sql database which will recreate all the system records that were removed.
After running the upgrader make sure to enable the importing of data if integrated with an ERP system.
To Delete Only Operational Data
Run the following script
select 'delete from ' + name from sys.tables where name like 'HZS%_1' OR name like 'HQMS_%_1'
NOTE: if you have transactional data that references the records in the operational data tables then running this query will make your system unstable and possibly even unuseable!
Important Notes:
If you want to keep:
- all your settings of how you setup the company,
- Any importing of records setups
- Any tracking number settings
Then you do not want to delete the HQMS_Setup table.
YOU MUST RUN the Upgrader application and execute the Install/Upgrade process on the sql database which will recreate all the system records that were removed.
After running the upgrader make sure to enable the importing of data if integrated with an ERP system.