sqlorcl

Just another WordPress.com site

Category Archives: Schema & Constraints

Refresh a schema in a database

We have several databases and we need to refresh only some Schemas not the entire database periodically. Objects of the schema are not changed that frequent so schema refresh only deal with data. Some of the tables in the schema are extremely large and deleting the rows from these tables will take a very long time and the delete operation will generate large log file. Best solution would be to truncate all tables of a respective schema and insert data from source tables. We decide to follow the steps describing below for schema refresh.

  1. Scripts out all existing foreign key constraints and indexed views to all tables in a particular schema to a temp table.
  • This temp table will contain scripts to drop schema, create schema and truncate table command.
  • Truncate table operation will be failed if that table is reference to a foreign key constraints or indexed view.  So we need to drop these constraints or indexed view if there anything present.
  • Drop any merge or transactional replication setting to the table. Fortunately we don’t have any replication setting to our tables. Replication setting will fail the table TRUNCATION.

There is a great post by Greg Robidoux how to scripts out the Enable, Disable, Drop and Recreate FKs based on Primary Key table. We are using this script and modified a portion of the script for our requirement. I added the modified version of the scripts in this blog.

DECLARE @tableName sysname
DECLARE @schemaName sysname
DECLARE @ID INT
SET @schemaName = ‘XXX’
DECLARE @cmd NVARCHAR(1000)
DECLARE @sql_DROP nvarchar(150)
DECLARE @sql_Trunc nvarchar(150)
DECLARE @sql_Create nvarchar(500)
 
IF OBJECT_ID(‘tempdb..#FkTable’) IS NOT NULL
DROP TABLE #FkTable
 
CREATE TABLE #FkTable
(
ID INT IDENTITY(1,1),
sql_DROP nvarchar(150),
sql_Trunc nvarchar(150),
sql_Create nvarchar(500)
)
 
DECLARE
@FK_NAME sysname,
@FK_OBJECTID INT,
@FK_DISABLED INT,
@FK_NOT_FOR_REPLICATION INT,
@DELETE_RULE smallint,
@UPDATE_RULE smallint,
@FKTABLE_NAME sysname,
@FKTABLE_OWNER sysname,
@PKTABLE_NAME sysname,
@PKTABLE_OWNER sysname,
@FKCOLUMN_NAME sysname,
@PKCOLUMN_NAME sysname,
@CONSTRAINT_COLID INT
 
DECLARE cursor_fkeys CURSOR FOR
SELECT Fk.name,
Fk.OBJECT_ID,
Fk.is_disabled,
Fk.is_not_for_replication,
Fk.delete_referential_action,
Fk.update_referential_action,
OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,
schema_name(Fk.schema_id) AS Fk_table_schema,
TbR.name AS Pk_table_name,
schema_name(TbR.schema_id) Pk_table_schema
FROM sys.foreign_keys Fk LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id –inner join
WHERE schema_name(TbR.schema_id) = @schemaName
 
OPEN cursor_fkeys
FETCH NEXT FROM cursor_fkeys
INTO @FK_NAME,@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
— create statement for dropping FK and also for recreating FK
— drop statement
SET @sql_DROP = ‘ALTER TABLE [‘ + @FKTABLE_OWNER + ‘].[‘ + @FKTABLE_NAME
+ ‘] DROP CONSTRAINT [‘ + @FK_NAME + ‘]’
 
SET @sql_Trunc = ‘TRUNCATE TABLE [‘ + @FKTABLE_OWNER + ‘].[‘ + @FKTABLE_NAME + ‘]’
— PRINT @sql_DROP
 
— create process
 
DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT
— create cursor to get FK columns
 
DECLARE cursor_fkeyCols CURSOR FOR
SELECT COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,
COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name
FROM sys.foreign_keys Fk LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN
sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID
WHERE TbR.name = @PKTABLE_NAME
AND schema_name(TbR.schema_id) = @schemaName
AND Fk_Cl.constraint_object_id = @FK_OBJECTID — added 6/12/2008
ORDER BY Fk_Cl.constraint_column_id
 
OPEN cursor_fkeyCols
 
FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
 
SET @COUNTER = 1
SET @FKCOLUMNS = ”
SET @PKCOLUMNS = ”
 
WHILE @@FETCH_STATUS = 0
BEGIN
IF @COUNTER > 1
BEGIN
SET @FKCOLUMNS = @FKCOLUMNS + ‘,’
SET @PKCOLUMNS = @PKCOLUMNS + ‘,’
END
SET @FKCOLUMNS = @FKCOLUMNS + ‘[‘ + @FKCOLUMN_NAME + ‘]’
SET @PKCOLUMNS = @PKCOLUMNS + ‘[‘ + @PKCOLUMN_NAME + ‘]’
SET @COUNTER = @COUNTER + 1
 
FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
END
CLOSE cursor_fkeyCols
DEALLOCATE cursor_fkeyCols
— generate create FK statement
SET @sql_Create = ‘ALTER TABLE [‘ + @FKTABLE_OWNER + ‘].[‘ + @FKTABLE_NAME + ‘] WITH ‘ +
CASE @FK_DISABLED
WHEN 0 THEN ‘ CHECK ‘
WHEN 1 THEN ‘ NOCHECK ‘
END + ‘ ADD CONSTRAINT [‘ + @FK_NAME
+ ‘] FOREIGN KEY (‘ + @FKCOLUMNS
+ ‘) REFERENCES [‘ + @PKTABLE_OWNER + ‘].[‘ + @PKTABLE_NAME + ‘] (‘
+ @PKCOLUMNS + ‘) ON UPDATE ‘ +
CASE @UPDATE_RULE
WHEN 0 THEN ‘ NO ACTION ‘
WHEN 1 THEN ‘ CASCADE ‘
WHEN 2 THEN ‘ SET_NULL ‘
END + ‘ ON DELETE ‘ +
CASE @DELETE_RULE
WHEN 0 THEN ‘ NO ACTION ‘
WHEN 1 THEN ‘ CASCADE ‘
WHEN 2 THEN ‘ SET_NULL ‘
END + ” +
CASE @FK_NOT_FOR_REPLICATION
WHEN 0 THEN ”
WHEN 1 THEN ‘ NOT FOR REPLICATION ‘
END
—PRINT @ql_Create
 
INSERT INTO #FkTable( sql_DROP,sql_Trunc,sql_Create ) VALUES ( @sql_DROP,@sql_Trunc, @sql_Create )
FETCH NEXT FROM cursor_fkeys
INTO @FK_NAME,@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER
END
CLOSE cursor_fkeys
DEALLOCATE cursor_fkeys
DECLARE cursor_fkeysCmd CURSOR FOR
SELECT sql_Trunc,sql_DROP FROM #FkTable
OPEN cursor_fkeysCmd
FETCH NEXT FROM cursor_fkeysCmd INTO @sql_Trunc,@sql_DROP
 
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @sql_DROP
EXEC sp_executesql @sql_Trunc
 
FETCH NEXT FROM cursor_fkeysCmd INTO @sql_Trunc,@sql_DROP
END
 
CLOSE cursor_fkeysCmd
DEALLOCATE cursor_fkeysCmd
 
  • Find the tables have identity field and SET IDENTITY INSERT ON to these tables to allow the insert values to IDENTITY columns.
DECLARE @sql_Table nvarchar(100)
DECLARE @sql_Create nvarchar(150)
 
DECLARE cursor_idyTable CURSOR FOR
SELECT ‘[‘ + SCHEMA_NAME(OBJECTPROPERTY(OBJECT_ID,’SCHEMAID’)) + ‘].[‘ + OBJECT_NAME(OBJECT_ID) + ‘]’ as TABLE__NAME
FROM SYS.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID,NAME, ‘IsIdentity’) = 1
AND SCHEMA_NAME (OBJECTPROPERTY(OBJECT_ID,’SCHEMAID’)) = ‘XXX’
OPEN cursor_idyTable
 
FETCH NEXT FROM cusrsor_idyTable INTO @sql_Table
 
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_Create = ‘SET IDENTITY_INSERT ‘ + @sql_Table + ‘ ON’
EXEC sp_execcutesql @sql_Create
FETCH NEXT FROM cusror_idyTable INTO @sql_Table
END
 
CLOSE cusror_idyTable
DEALLOCATE cusror_idyTable
  • Using Database Export Import wizard we import data to every tables in the specific schema.
  • Recreate the foreign key constants scripts taken on step 2.
DECLAREcursor_fkeysCmdCURSORFOR
SELECTsql_CreateFROM#FkTable
OPENcursor_fkeysCmd 
 
FETCHNEXTFROMcursor_fkeysCmdINTO@sql_Create
  
WHILE@@FETCH_STATUS= 0  
BEGIN
       EXECsp_executesql @sql_Create
       FETCHNEXTFROMcursor_fkeysCmdINTO@sql_Create
END
      
CLOSEcursor_fkeysCmd  
DEALLOCATEcursor_fkeysCmd
  • SET IDENTITY INSERT OFF to all IDENTITY tables in specific schema.

Enjoy!!!!

Advertisements