How to get DROP AND CREATE script for all TABLE(s) with DEFAULT CONSTRAINTS USING simple SELECT in SQL SERVER

Below T-SQL QUERY helps to get DROP and CREATE script for all TABLE(s) with DEFAULT CONSTRAINTS USING simple SELECT in SQL SERVER. Feel free to use it if needed,  Kindly let me know in case if you got a better and easy way of doing it (do not say 😊 that it can be done using SSMS, right-click on the database-->Tasks-->Generate Scripts...).

SELECT DISTINCT 'IF EXISTS (SELECT * FROM AdventureWorksDW2017.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '''+ST.TABLE_SCHEMA+''' AND TABLE_NAME = '''+ST.TABLE_NAME+''' AND TABLE_CATALOG =''AdventureWorksDW2017'') DROP TABLE AdventureWorksDW2017.'+QUOTENAME(ST.TABLE_SCHEMA)+'.'+QUOTENAME(ST.TABLE_NAME)+ CHAR(13)+'CREATE TABLE AdventureWorksDW2017.'+QUOTENAME(ST.TABLE_SCHEMA)+'.'+QUOTENAME(ST.TABLE_NAME)+' ( ' +ISNULL(STUFF(REPLACE((SELECT '~~' + QUOTENAME(C.COLUMN_NAME) + ' ' +DATA_TYPE +CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+ CASE WHEN CHARACTER_MAXIMUM_LENGTH ='-1' THEN CAST('MAX' AS VARCHAR) ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE ' ' END+ ' '+CASE WHEN  COLUMNPROPERTY(OBJECT_ID(QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME)), C.COLUMN_NAME, 'ISIDENTITY') =1 THEN    'IDENTITY(' +     CAST(IDENT_SEED(ST.TABLE_NAME) AS VARCHAR) + ',' +     CAST(IDENT_INCR(ST.TABLE_NAME) AS VARCHAR) + ')'   ELSE ''   END + ' ' +( CASE WHEN C.IS_NULLABLE = 'NO' THEN 'NOT ' ELSE '' END ) + 'NULL '+ CASE WHEN C.COLUMN_DEFAULT IS NOT NULL THEN ' CONSTRAINT '+DEF_CONS.CONSTRAINT_NAME+' DEFAULT ' +C.COLUMN_DEFAULT ELSE '' END+ ' 'AS 'data()' FROM INFORMATION_SCHEMA.TABLES T INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_SCHEMA=C.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAMELEFT JOIN (SELECT SDC.name CONSTRAINT_NAME,SCH.name AS TABLE_SCHEMA   ,ST.name TABLE_NAME,SC.name COLUMN_NAME FROM SYS.COLUMNS  AS SC INNER JOIN SYS.TABLES AS ST ON ST.OBJECT_ID = SC.OBJECT_ID INNER JOIN SYS.SCHEMAS AS SCH ON SCH.SCHEMA_ID = ST.SCHEMA_ID INNER JOIN SYS.default_constraints AS SDC ON SDC.parent_object_id = SC.object_id AND SDC.parent_column_id = SC.column_id  WHERE  SDC.name IS NOT NULL ) DEF_CONS ON DEF_CONS.TABLE_SCHEMA =C.TABLE_SCHEMA AND DEF_CONS.TABLE_NAME=C.TABLE_NAME  AND DEF_CONS.COLUMN_NAME = C.COLUMN_NAME   WHERE T.TABLE_NAME = SC.TABLE_NAME AND T.TABLE_SCHEMA = SC.TABLE_SCHEMA FOR XML PATH('')),'~~',', '), 1, 2, ''),'') + ')' --as [TABLE_CREATE_SCRIPT]  FROM INFORMATION_SCHEMA.TABLES ST INNER JOIN INFORMATION_SCHEMA.COLUMNS SC ON ST.TABLE_SCHEMA=SC.TABLE_SCHEMA AND ST.TABLE_NAME = SC.TABLE_NAME WHERE ST.TABLE_TYPE ='BASE TABLE' AND ST.TABLE_NAME <> 'sysdiagrams'

I have executed it under AdventureWorks2017 sample database. Below is the output

The above script would only provide the script to drop and create the table. If the table is foreign key referenced. You will have to drop the foreign key and referenced keys before dropping the table. Below script can be used to drop and create primary and unique keys.

USE AdventureWorksDW2017

GO

SELECT TABLE_SCHEMA,TABLE_NAME,INDEX_NAME CONSTRAINT_NAME,'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE CONSTRAINT_NAME = '''+INDEX_NAME+''' AND TABLE_SCHEMA = '''+TABLE_SCHEMA+''' AND TABLE_NAME = '''+TABLE_NAME+''' AND TABLE_CATALOG =''AdventureWorksDW2017'') ALTER TABLE '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+' DROP CONSTRAINT'+ QUOTENAME(INDEX_NAME) + CHAR(13)+'ALTER TABLE '+  QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+ ' ADD CONSTRAINT ' +  QUOTENAME(INDEX_NAME) + IS_UNIQUE_CONSTRAINT + IS_PRIMARY_KEY + INDEX_TYPE_DESC +  '('+[INDEX_COLUMNS]+' ) '-- +case when len([INCLUDED_COLUMNS])>0 then CHAR(13) +'INCLUDE (' + [INCLUDED_COLUMNS]+ ')' else '' end + CHAR(13)+'WITH (' + INDEXOPTIONS+ ') ON ' + QUOTENAME(FILEGROUPNAME) + ';'    AS SQLCMD --INTO ##PRIMARYKEY_CONSTRAINTS   FROM (SELECT DISTINCT SCHEMA_NAME(ST.SCHEMA_ID) TABLE_SCHEMA, ST.NAME TABLE_NAME, SIX.NAME INDEX_NAME,CASE WHEN SIX.IS_UNIQUE = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE,CASE WHEN SIX.IS_UNIQUE_CONSTRAINT = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE_CONSTRAINT,CASE WHEN SIX.IS_PRIMARY_KEY = 1 THEN ' PRIMARY KEY ' ELSE '' END AS IS_PRIMARY_KEY, SIX.TYPE_DESC COLLATE DATABASE_DEFAULT  INDEX_TYPE_DESC,  CASE  WHEN SIX.IS_PADDED=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END  + CASE WHEN SIX.ALLOW_PAGE_LOCKS=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END  + CASE WHEN SIX.ALLOW_ROW_LOCKS=1 THEN  'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END  + CASE WHEN INDEXPROPERTY(ST.OBJECT_ID, SIX.NAME, 'ISSTATISTICS') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END  + CASE WHEN SIX.IGNORE_DUP_KEY=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF ' END  AS INDEXOPTIONS,FILEGROUP_NAME(SIX.DATA_SPACE_ID) FILEGROUPNAME  ,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME)+ ' '+ CASE WHEN IXC.IS_DESCENDING_KEY =1 THEN 'DESC' ELSE 'ASC' END  AS 'data()'  FROM SYS.TABLES TJOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_IDJOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_IDJOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID  WHERE IX.TYPE>0 AND (SIX.IS_PRIMARY_KEY=1  OR SIX.IS_UNIQUE_CONSTRAINT=1) AND IXC.IS_INCLUDED_COLUMN <> 1AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAMEFOR XML PATH('')),'~~',', '), 1, 2, '') as [INDEX_COLUMNS],ISNULL(STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME) AS 'data()' FROM SYS.TABLES TJOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_IDJOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_IDJOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID  WHERE IX.TYPE>0 AND (SIX.IS_PRIMARY_KEY=1  OR SIX.IS_UNIQUE_CONSTRAINT=1) AND IXC.IS_INCLUDED_COLUMN = 1AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAMEFOR XML PATH('')),'~~',', '), 1, 2, ''),'') as [INCLUDED_COLUMNS]  FROM SYS.TABLES ST INNER JOIN SYS.INDEXES SIX ON ST.OBJECT_ID=SIX.OBJECT_ID  WHERE SIX.TYPE>0 AND (  SIX.IS_PRIMARY_KEY=1  OR SIX.IS_UNIQUE_CONSTRAINT=1)  AND ST.IS_MS_SHIPPED=0 AND ST.NAME<>'SYSDIAGRAMS'  ) A

I have executed it under AdventureWorks2017 sample database. Below is the output

Below script can be used to drop and create Indexes.

USE AdventureWorksDW2017

GO

SELECT INDEX_NAME,'IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = '''+INDEX_NAME+''') DROP INDEX '+QUOTENAME(INDEX_NAME)+' ON  ' + QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+' CREATE '+ IS_UNIQUE  +INDEX_TYPE_DESC + ' INDEX ' +QUOTENAME(INDEX_NAME)+' ON ' + QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+ '('+[INDEX_COLUMNS]+' ) '+  case when [INCLUDED_COLUMNS]<>'' then CHAR(13) +'INCLUDE (' + [INCLUDED_COLUMNS]+ ')' else '' end + CHAR(13)+'WITH (' + INDEXOPTIONS+ ') ON ' + QUOTENAME(FILEGROUPNAME) + ';' SQLCMD  FROM (SELECT DISTINCT SCHEMA_NAME(ST.SCHEMA_ID) TABLE_SCHEMA, ST.NAME TABLE_NAME, SIX.NAME INDEX_NAME,CASE WHEN SIX.IS_UNIQUE = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE, SIX.TYPE_DESC COLLATE DATABASE_DEFAULT  INDEX_TYPE_DESC,  CASE  WHEN SIX.IS_PADDED=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END  + CASE WHEN SIX.ALLOW_PAGE_LOCKS=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END  + CASE WHEN SIX.ALLOW_ROW_LOCKS=1 THEN  'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END  + CASE WHEN INDEXPROPERTY(ST.OBJECT_ID, SIX.NAME, 'ISSTATISTICS') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END  + CASE WHEN SIX.IGNORE_DUP_KEY=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF ' END  AS INDEXOPTIONS,FILEGROUP_NAME(SIX.DATA_SPACE_ID) FILEGROUPNAME  ,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME)+ ' '+ CASE WHEN IXC.IS_DESCENDING_KEY =1 THEN 'DESC' ELSE 'ASC' END  AS 'data()'FROM SYS.TABLES TJOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_IDJOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_IDJOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID  WHERE IX.TYPE>0 AND (IX.IS_PRIMARY_KEY=0 AND IX.IS_UNIQUE_CONSTRAINT=0) AND IXC.IS_INCLUDED_COLUMN <> 1AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAMEFOR XML PATH('')),'~~',', '), 1, 2, '') as [INDEX_COLUMNS],ISNULL(STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME) AS 'data()' FROM SYS.TABLES TJOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_IDJOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_IDJOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID  WHERE IX.TYPE>0 AND (IX.IS_PRIMARY_KEY=0 AND IX.IS_UNIQUE_CONSTRAINT=0) AND IXC.IS_INCLUDED_COLUMN = 1AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAMEFOR XML PATH('')),'~~',', '), 1, 2, ''),'') as [INCLUDED_COLUMNS]  FROM SYS.TABLES ST INNER JOIN SYS.INDEXES SIX ON ST.OBJECT_ID=SIX.OBJECT_ID  WHERE SIX.TYPE>0 AND ( SIX.IS_PRIMARY_KEY=0  AND SIX.IS_UNIQUE_CONSTRAINT=0)  AND ST.IS_MS_SHIPPED=0 AND ST.NAME<>'SYSDIAGRAMS'  ) A

Please feel free to use the scripts when in need.

How to implement Archive and Purge solution for all Fact and dimension tables data in DW database

SCENARIO:

BI Developer(s) often heard performance issues of BI applications from the customers because of several reasons. One of the most common reasons is the volume of the data.  Over time, underlying databases of BI applications would grow to the extent that would degrade the performance of the applications. Generally, developer(s) would never implement a solution to purge the data from any OLAP system because of the more the data, the prediction would be accurate.

In this article, I will explain how to implement Archive and Purge solution for the data warehouse. My idea is to have one configuration table which stores details of key columns, table details, batch size and the retention period etc to keep the data in online warehouse database based on the retention period and copy rest of the data to archive warehouse database based on batch size and purge the same from online warehouse database. I will be attaching the script as I go over the steps.

The goal is to move the cold or old data from the current warehouse database to archive warehouse database based on retention settings mentioned in the configuration table.

I have presumed the current warehouse database does not have any foreign key constraints created between the fact and dimension tables. I am using "AdventureWorksDW2017" database to implement this. As this sample database has foreign key constraints, I would be disabling all the constraints.

IMPLEMENTATION:

Let's implement it.

1. Download AdvenutureWorksDW2017 database from here. Restore it to your SQL instance.

2. Copy the following script "CREATE CONFIGURATION ENTRIES.sql" and execute it in SQL Instance. This script would do the following things

• Clone the "AdventureWorksDW2017" to "AdventureWorksDW2017_Archive". Not necessary to clone, create the archive database manually and also create the tables that are needed to move the database from current to archive.
• Create ArchiveFactTablesConfig table to store the configuration settings such as retention period, batch size, key columns, filtered column, online database and archive database etc
• Add sample entries to the configuration table for few fact tables (since this is one time you can add all necessary tables configuration manually).
• Add (AdventureWorksDW_Archive_Log) logging table which tracks the data movement between current and archive data warehouse fact tables.
• Disable all constraints in the database

3. Let's check the ArchiveFactTablesConfig and logging table after executing Step 2. Here, 7 sample tables under 3 bucket groups. The idea is to have a bucket group column in the configuration table to execute the stored procedure concurrently for each bucket group.

4. Copy the following script "sp_PurgeFactTablesData.SQL" and execute it in SQL Instance. This script would do the following things

• Add Archive Schema to AdventureWorksDW2017 database
• Create ARCHIVE.sp_PurgeFactTablesData stored procedure which would accept TABLE_NAME as a parameter. This would  
• read all settings from the configuration table based on the table name (table name must be unique)
• copy the data from current to archive based on retention settings
• purge the data from the current warehouse database.
• add logging entries to AdventureWorksDW_Archive_Log for tracking purpose.

5. Now we have set up configuration tables and the stored procedure is also available in the current warehouse database. In order to execute the stored procedure concurrently for each bucket group then SSIS package is the better approach.

Before going to the approach, Let's check row count of one fact table.  As I have set RETENTION_PERIOD to 3225 as I would like to keep 3225 days of data in online warehouse database and rest of the data like to move to archive database and purged from the online database. We can use same query to check after the execution of the SSIS package.

SELECT TABLE_NAME,FILTER_COLUMN,KEY_COLUMNS,RETENTION_PERIOD,BATCH_SIZE,BUCKET_GROUP,ISACTIVE,LAST_UPDATE,LAST_OUTCOME
FROM AdventureWorksDW2017.dbo.[ArchiveFactTablesConfig]

SELECT * FROM AdventureWorksDW2017.dbo.AdventureWorksDW_Archive_Log

SELECT OrderDate,COUNT(*) AS ROWCNT FROM AdventureWorksDW2017.dbo.FactInternetSales WHERE  DATEDIFF(DD,OrderDate,GETDATE()) > 3225 GROUP BY OrderDate

SELECT OrderDate,COUNT(*) AS ROWCNT FROM AdventureWorksDW2017_Archive.dbo.FactInternetSales WHERE  DATEDIFF(DD,OrderDate,GETDATE()) > 3225 GROUP BY OrderDate

SSIS Approach: Create a Job to call SSIS package from SQL Server Agent and schedule as needed. Copy the following SSIS package "Archive Fact Data.dtsx"  and change the database connection. In order to change the database connection, update the "AdventureWorksDWConn" variable value correctly in the package.

Before executing the package, please make sure you correctly set the FILTER_COLUMN, KEY_COLUMNS, RETENTION_PERIOD and BATCH_SIZE etc.

Let's execute the SSIS package

Let's check the configuration to check the status and logging tables to check row count. Use the same query to check

SELECT TABLE_NAME,FILTER_COLUMN,KEY_COLUMNS,RETENTION_PERIOD,BATCH_SIZE,BUCKET_GROUP,ISACTIVE,LAST_UPDATE,LAST_OUTCOME FROM AdventureWorksDW2017.dbo.[ArchiveFactTablesConfig]

SELECT * FROM AdventureWorksDW2017.dbo.AdventureWorksDW_Archive_Log WHERE TABLE_NAME = 'FactInternetSales'

SELECT OrderDate,COUNT(*) AS ROWCNT FROM AdventureWorksDW2017.dbo.FactInternetSales WHERE  DATEDIFF(DD,OrderDate,GETDATE()) > 3225
GROUP BY OrderDate

SELECT OrderDate,COUNT(*) AS ROWCNT FROM AdventureWorksDW2017_Archive.dbo.FactInternetSales WHERE  DATEDIFF(DD,OrderDate,GETDATE()) > 3225
GROUP BY OrderDate

With this, you can say that the fact table records copied to an archive database and purged from online warehouse database.

6. Another approach is to use the below code to call "sp_PurgeFactTablesData" stored procedure.

SQL Approach:

Create a SQL Agent job to call below T-SQL code. This approach would be helpful if you would like to load the table records based on the table dependency order and needed to move/process the records sequentially one table after other. You can add one more column in the [ArchiveFactTablesConfig] table to store parent/child table dependency order. You will have to tweak below code based on requirements.

USE AdventureWorksDW2017

GO

IF (OBJECT_ID('tempdb..#Tablenames') IS NOT NULL) DROP TABLE #Tablenames

SELECT TABLE_NAME INTO #Tablenames  FROM  dbo.[ArchiveFactTablesConfig] (NOLOCK) --ORDER BY DEPENDENCY_ORDER

WHILE EXISTS (SELECT * FROM #Tablenames)
BEGIN
DECLARE @TABLE_NAME SYSNAME
SELECT TOP 1 @TABLE_NAME =TABLE_NAME FROM #Tablenames
EXEC ARCHIVE.sp_PurgeFactTablesData @TABLE_NAME
DELETE #Tablenames WHERE TABLE_NAME = @TABLE_NAME
END

7.  Please make sure to do the full process of cubes in case if BI applications are connected to Cubes. If users like to analyse the historical data then users will have to connect to the archive database and process the data when needed.

You can download this code from GitHub too
Kindly let me know if there is any better way.

Thanks