Before You Begin.
IMPORTANT. IF your Schema Profile is assigned to run in one of the four Windows services you should:
•Stop The Services (If Running).
•Stop ANY other process(es) which may be accessing your Schema Tables, like the CORE HL7 Postmaster for instance, or your own processes.
In the course of working with your CORE HL7 MS SQL Schema Engine you may want to Truncate all data from your Schema Tables WITHOUT removing tables (dropping them) so that all of the alterations made during Schema Training are preserved. Below is an example of a stored procedure you can use which when called will safely truncate all CORE HL7 Schema data while preserving mandatory records.
STORED PROCEDURE:
CREATE PROCEDURE [dbo].[TruncateHL7Schema]
@Prefix VARCHAR(4)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TableName NVARCHAR(255);
DECLARE @Sql NVARCHAR(MAX);
DECLARE @FullPrefix NVARCHAR(8); -- Adjusted length to accommodate up to 4 chars + underscore
-- Form the full prefix by appending an underscore to the provided prefix
SET @FullPrefix = @Prefix + '[_]';
-- Cursor to iterate over all table names that match the prefix and exclude 'PREFIX_SchemaMaintenance'
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE @FullPrefix + '%' ESCAPE '\'
AND TABLE_NAME != @Prefix + '_SchemaMaster';
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Construct the TRUNCATE TABLE statement
SET @Sql = 'TRUNCATE TABLE ' + QUOTENAME(@TableName);
-- Execute the TRUNCATE TABLE statement
EXEC sp_executesql @Sql;
FETCH NEXT FROM TableCursor INTO @TableName;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
END;
GO
EXECUTION:
/* Where your Schema Prefix is AA */
EXEC TruncateHL7Schema 'AA'
