Decimal Data Type Variable in SSIS - SSIS Tutorial

In previous version of SQL Server Integration Services (SSIS) Decimal data type was not available for Variables. In SSIS 2012 Microsoft introduced Decimal data type as shown in below snapshot.

What is decimal data type variable in SSIS 

The value range that it can store is:

0 through +/-79,228,162,514,264,337,593,543,950,335 with no decimal point; 
0 through +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest nonzero number is 
+/-0.0000000000000000000000000001 (+/-1E-28).

Confusion:
 If we try to provide value greater than 79,228,162,514,264,337,593,543,950,335 it does throw an error. The value is too small or too large but if we provide  smaller value than 0.0000000000000000000000000001 it does not throw any error. Later if we use the variable in expressions it will round the value for variable as shown in below snapshot.
Decimal data type for variable in SSIS 


To see the value range for Variable data types , please visit

DBA - Maintenance Plan "Back Up Database Task" Copy-only Backup Option Included SQL Server 2012

We were able to Copy-only Backup  by using GUI ( SSMS) in SQL Server 2008 but It was not available in  Maintenance Plan "Back Up Database Task". Now in SQL Server 2012 It is included in "Back Up Database Task".

"Back up the tail of the log, and leave the database in the restoring state" Option  that was available in SQL server 2008 "Back Up Database Task" is now removed in SQL Server 2012 "Back Up Database Task" in Maintenance Plan.

Maintenance Plan--> Back Up Database Task SQL Server 2008/2008R2
How to take Copy Only backup in SQL Server 


Maintenance Plan--> Back Up Database Task SQL Server 2012
How to perform Copy-Only Backup in SQL Server

For more detail , please visit
http://msdn.microsoft.com/en-us/library/ms191495.aspx


How to Restore Database with Multiple Transaction Log Backup Files SQL Server - SQL Server DBA Tutorial

Scenario:


Let's say we have taken Full backup and multiple transaction log backups of database in one of the folder and now we want to restore them starting with full backup and then selected transaction log backup files.

Solution:


Step 1:
 In your SSMS, go to Object Explorer Detail by Going to View--> Object Explorer Detail or by hitting F7 Key.

Step 2:
In Object Explorer Detail pane, Right Click on Databases and Select Restore Database.

Step 3: 
Under the Source, Click on Device and then Add Files , Select All the backup files( Full backup+ Transaction log files those you want to restore)

Step4: 
Under Destination, provide the name of Database you want to have. Restore To will show "The last backup taken ....." that is the date time your last selected transaction log backup file. You can change the Timeline by clicking on TimeLine button and Select your criteria as you want.

Snapshot to Select Source Files and Destination Database Name

How to restore Database with Multiple Transaction Log backup files in SQL Server 

Timeline: You can select hour, six hours, day , week or provide date time by yourself to which point you want to restore your database from backup files.

How to Restore Multiple Transaction Log backup Files to Database in SQL Server - SQL Server DBA Tutorial

Scenario: 

You are working as SQL Server DBA, you run your full backup jobs and transaction log jobs on different schedules.
Now you need to restore database, you have restored the full backup in NoRecovery mode and need to restore set of log backups. It is really annoying to choose one log backup file at a time and keep restoring.

The below script can be used to restore multiple log backup files to database. You can change the select query to include the log backup files you like to restore.


/*--------------------------------------------------
Restore Multiple transaction log backup files
----------------------------------------------------*/
DECLARE @DBName VARCHAR(500)
DECLARE @LogFilePath VARCHAR(500)

SET @DBName='TestDB'

DECLARE LogR_cursor CURSOR FOR
  --Prepare your list of Log File those needs to be restored.
  -- Uncomment the columns while prepare your List of Log files.
  SELECT
  --BS.database_name,
  BMF.physical_device_name
  --,BS.backup_start_date
  -- ,BS.backup_finish_date
  FROM   msdb.dbo.backupset BS
         INNER JOIN msdb.dbo.backupmediafamily BMF
                 ON BS.media_set_id = BMF.media_set_id
  WHERE  BS.database_name = @DBName
         AND BS.Type = 'L'
  --AND BS.backup_start_date>=''   Provide the StartDate and EndDate To Refine your Selection for Log Files
  --AND BS.backup_finish_date<=''    those you want to restore.
  ORDER  BY backup_start_date,
            backup_finish_date

OPEN LogR_cursor

FETCH NEXT FROM LogR_cursor INTO @LogFilePath

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL NVARCHAR(2000)

      SET @SQL='Restore Log ' + @DBName + ' from Disk='''
               + @LogFilePath + ''' With NORECOVERY'

      PRINT @SQL
      EXEC(@SQL)

      FETCH NEXT FROM LogR_cursor INTO @LogFilePath
  END

--SET DATABASE Mode to Recovery
DECLARE @SQL_SET_RECOVERY NVARCHAR(2000)

SET @SQL_SET_RECOVERY='Restore Database ' + @DBName
                      + ' WITH RECOVERY'

PRINT @SQL_SET_RECOVERY
EXEC(@SQL_SET_RECOVERY)

CLOSE LogR_cursor

DEALLOCATE LogR_cursor

How to Estimate the Size of a Clustered Index in SQL Server

Scenario: 

You are working as SQL Server DBA or developer, You are working with storage estimation projection. You would like to estimate storage space for clustered index with given number or rows.


Solution:

Below script can be used to estimate storage size for cluster indexes. You can provide number of rows you would like to have in table on which clustered index is created.

--Drop and Create Temp Tables
IF Object_id(N'tempdb..#Index_Space_Used') IS NOT NULL
  BEGIN
      DROP TABLE #Index_Space_Used
  END

IF Object_id(N'tempdb..#Temp') IS NOT NULL
  BEGIN
      DROP TABLE #Temp
  END

CREATE TABLE #Index_Space_Used
  (
     Table_Name      VARCHAR(100),
     Num_Index_Pages INT
  )
/*----------------------------------------
1:Build the List of Tables with Rows for which Estimation have to be done
------------------------------------------*/
;

WITH CTE_TableNames
     AS (SELECT 'Table1'   AS TableName,
                12800 AS Num_Rows
         UNION ALL
         SELECT 'Table2' AS TableName,
                479232    AS Num_Rows
                UNION ALL
                SELECT 'Table3' AS TableName,
                360448   AS Num_Rows)
/*----------------------------------------
2:Get Storage Size for Fixed Column and Variable Columns
------------------------------------------*/
,
     CTE_DataTypes
     AS (SELECT TABLE_NAME,
                TABLE_SCHEMA,
                COLUMN_NAME,
                IS_NULLABLE,
                DATA_TYPE,
                CHARACTER_MAXIMUM_LENGTH,
                CASE
                  WHEN Data_Type = 'Int' THEN 4
                  WHEN DATA_TYPE = 'BigInt' THEN 8
                  WHEN DATA_TYPE = 'TinyInt' THEN 1
                  WHEN DATA_TYPE = 'SmallInt' THEN 2
                  WHEN DATA_TYPE = 'Bit' THEN 1
                  WHEN DATA_TYPE = 'Money' THEN 8
                  WHEN DATA_TYPE = 'SmallMoney' THEN 4
                  WHEN DATA_TYPE = 'SmallDateTime' THEN 4
                  WHEN DATA_TYPE = 'DateTime' THEN 8
                  WHEN DATA_TYPE = 'Date' THEN 3
                  WHEN DATA_TYPE = 'Real' THEN 4
                  WHEN DATA_TYPE = 'Float'
                       AND NUMERIC_PRECISION <= 24 THEN 4
                  WHEN DATA_TYPE = 'Float'
                       AND NUMERIC_PRECISION >= 25 THEN 8
                  WHEN DATA_TYPE IN ( 'Decimal', 'Numeric' )
                       AND NUMERIC_PRECISION <= 9 THEN 5
                  WHEN DATA_TYPE IN ( 'Decimal', 'Numeric' )
                       AND NUMERIC_PRECISION BETWEEN 10 AND 19 THEN 9
                  WHEN DATA_TYPE IN ( 'Decimal', 'Numeric' )
                       AND NUMERIC_PRECISION BETWEEN 20 AND 28 THEN 13
                  WHEN DATA_TYPE IN ( 'Decimal', 'Numeric' )
                       AND NUMERIC_PRECISION BETWEEN 29 AND 38 THEN 17
                  WHEN DATA_TYPE = 'Time'
                       AND DATETIME_PRECISION IN ( 0, 1, 2 ) THEN 3
                  WHEN DATA_TYPE = 'Time'
                       AND DATETIME_PRECISION IN ( 3, 4 ) THEN 4
                  WHEN DATA_TYPE = 'Time'
                       AND DATETIME_PRECISION >= 5 THEN 5
                  WHEN DATA_TYPE = 'DateTime2'
                       AND DATETIME_PRECISION < 3 THEN 6
                  WHEN DATA_TYPE = 'DateTime2'
                       AND DATETIME_PRECISION IN( 3, 4 ) THEN 7
                  WHEN DATA_TYPE = 'DateTime2'
                       AND DATETIME_PRECISION > 4 THEN 8
                  WHEN DATA_TYPE = 'DateTimeoffset'
                       AND DATETIME_PRECISION < 3 THEN 8
                  WHEN DATA_TYPE = 'DateTimeoffset'
                       AND DATETIME_PRECISION IN( 3, 4 ) THEN 9
                  WHEN DATA_TYPE = 'DateTimeoffset'
                       AND DATETIME_PRECISION > 4 THEN 10
                  ELSE CHARACTER_OCTET_LENGTH --This is going to cover CHAR,NCHAR,VARCHAR,NVARCHAR Columns
                END AS SIZE_IN_BYTES
         FROM   INFORMATION_SCHEMA.COLUMNS),
     CTE_Num_Cols
     AS (SELECT TD.Table_NAME,
                Count(TD.COLUMN_NAME) Num_Cols,
                Sum(SIZE_IN_BYTES)    AS SIZE_IN_BYTES,
                TN.Num_Rows
         FROM   CTE_DataTypes TD
                INNER JOIN CTE_TableNames TN
                        ON TD.TABLE_NAME = TN.TableName
         GROUP  BY TD.Table_NAME,
                   TN.Num_Rows),
     NullBitMap_RowSize
     AS (SELECT Table_Name,
                Num_Rows,
                Num_Cols,
                Size_In_Bytes,
                2 + ( ( Num_Cols + 7 ) / 8 )                         AS NullBitMap,
                Size_In_Bytes + ( 2 + ( ( Num_Cols + 7 ) / 8 ) ) + 4 AS Row_Size,
                Size_In_Bytes + ( 2 + ( ( Num_Cols + 7 ) / 8 ) ) + 4 + 1--(for row header overhead of an index row)
                + 7--(for the child page ID pointer)
                                                                     AS Index_Row_Size
         FROM   CTE_Num_Cols),
     Rows_Per_Page
     AS (SELECT TABLE_NAME,
                Num_Rows,
                Num_Cols,
                SIZE_IN_BYTES,
                NullBitMap,
                Row_Size,
                8096 / ( Row_Size + 2 )       AS Rows_Per_Page,
                Index_Row_Size,
                8096 / ( Index_Row_Size + 2 ) AS Index_Rows_Per_Page
         FROM   NullBitMap_RowSize),
     Free_Rows_Per_Page
     AS (SELECT TABLE_NAME,
                Num_Rows,
                Num_Cols,
                SIZE_IN_BYTES,
                NullBitMap,
                Row_Size,
                Rows_Per_Page,
                Ceiling(8096 * ( ( 100 - 95 ) / 100.0 ) / ( Row_Size + 2 )) AS Free_Rows_Per_Page,
                Index_Row_Size,
                Index_Rows_Per_Page
         FROM   Rows_Per_Page),
     Num_Leaf_Pages
     AS (SELECT TABLE_NAME,
                Num_Rows,
                Num_Cols,
                SIZE_IN_BYTES,
                NullBitMap,
                Row_Size,
                Rows_Per_Page,
                Free_Rows_Per_Page,
                Ceiling(Num_Rows / ( Rows_Per_Page - Free_Rows_Per_Page )) AS Num_Leaf_Pages,
                Index_Row_Size,
                Index_Rows_Per_Page
         FROM   Free_Rows_Per_Page),
     Leaf_Space_Used
     AS (SELECT TABLE_NAME,
                Num_Rows,
                Num_Cols,
                SIZE_IN_BYTES,
                NullBitMap,
                Row_Size,
                Rows_Per_Page,
                Free_Rows_Per_Page,
                Num_Leaf_Pages,
                8192 * Num_Leaf_Pages                                                           AS Leaf_Space_used_In_Bytes,
                Ceiling(( 8192 * Num_Leaf_Pages ) / 1024)                                       AS Leaf_Space_used_in_KB,
                Index_Row_Size,
                Index_Rows_Per_Page,
                Ceiling(1 + Log (Index_Rows_Per_Page) * ( Num_Leaf_Pages / Index_Rows_Per_Page )) AS Non_leaf_Levels
         FROM   Num_Leaf_Pages)
SELECT *
INTO   #Temp
FROM   Leaf_Space_Used

--Calculate the number of non-leaf pages
DECLARE @TableName VARCHAR(100)
DECLARE Table_Cursor CURSOR FOR
  SELECT Table_Name
  FROM   #Temp

OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @Cnt INT
      DECLARE @Num_Leaf_Pages INT
      DECLARE @Non_Leaf_Levels INT
      DECLARE @Index_Row_Per_Page INT
      DECLARE @VarSUM INT

      SET @VarSUM=0

      SELECT @Num_Leaf_Pages = Num_Leaf_Pages,
             @Non_Leaf_Levels = Non_Leaf_Levels,
             @Index_Row_Per_Page = Index_Rows_Per_Page
      FROM   #Temp
      WHERE  TABLE_NAME = @TableName

      WHILE ( @Non_Leaf_Levels <> 0 )
        BEGIN
            BEGIN TRY
                SET @VarSUM=@VarSUM + ( Ceiling(Cast(@Num_Leaf_Pages AS NUMERIC(38, 0)) / Power(Cast(@Index_Row_Per_Page AS NUMERIC(38, 0)), @Non_Leaf_Levels)) )
            END TRY

            BEGIN CATCH
            END CATCH

            SET @Non_Leaf_Levels=@Non_Leaf_Levels - 1
        END

      INSERT INTO #Index_Space_Used
      VALUES      (@TableName,
                   @VarSUM)

      FETCH NEXT FROM Table_Cursor INTO @TableName
  END

CLOSE Table_Cursor

DEALLOCATE Table_Cursor

SELECT T.*,
       ISU.Num_Index_Pages,
       T.Leaf_Space_used_in_KB
       + Ceiling(((8192*ISU.Num_Index_Pages)/1024)) AS Clustered_Index_Size_in_KB
FROM   #Temp T
       INNER JOIN #Index_Space_Used ISU
               ON T.TABLE_NAME = ISU.Table_Name

How to Add OR Drop Column From CDC Enabled Table Without Losing Data in SQL Server Database - SQL Server Tutorial

Scenario:  Download Script

Sometime we have to add or drop column to Source table on which Change Data Capture (CDC) is already enabled.
The New Added column will not be tracked by Change Data Capture(CDC) and for Dropped column, CDC table will show Null.

The SP can be used to Add New column to Change Data Capture (CDC) without losing Any previous data in CDC Table. The same SP can be used to drop column from Change Data Capture (CDC)  as It is dropped from Source Column but leave the remaining columns without losing any Data in CDC Table.


USE [DataBaseName]

GO

/*--------------------------------------------------------------------------------------------------------
How to Execute: EXEC usp_AddOrDropColumnToCDCTableWithoutLosingData @SchemaName,@TableName,@ColumnName,@Action

Adding New Column
Example :EXEC usp_AddOrDropColumnToCDCTableWithoutLosingData 'dbo','T','NAME','ADD'

Drop existing Column
Example :EXEC usp_AddOrDropColumnToCDCTableWithoutLosingData 'dbo','T','Name','DROP'

--------------------------------------------------------------------------------------------------------*/
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE dbo.Usp_addordropcolumntocdctablewithoutlosingdata @pSchemaName VARCHAR(50),
                                                                    @pTableName  VARCHAR(100),
                                                                    @pColumnName VARCHAR(100),
                                                                    @pAction     VARCHAR(10)
AS
  BEGIN
      DECLARE @vSQLTempTable NVARCHAR(MAX)
      DECLARE @vSQLAlterTable NVARCHAR(MAX)
      DECLARE @vDisableCDC NVARCHAR(MAX)
      DECLARE @vGetAlreadyExistingColumns NVARCHAR(MAX)
      DECLARE @vEnableCDC NVARCHAR(MAX)
      DECLARE @vLoadCDCTable NVARCHAR(MAX)
      DECLARE @vDropCreateTemp NVARCHAR(MAX)
      DECLARE @vDataType VARCHAR(50)
      DECLARE @vColumnList NVARCHAR(2000)
      DECLARE @vColumnExists INT
      DECLARE @vColumnExistsCDC INT
      DECLARE @vTempTableName VARCHAR(100)
      DECLARE @vDropColumnList NVARCHAR(2000)
      DECLARE @vDropColumnListExcSys NVARCHAR(2000)

      SET @vTempTableName='##' + @pSchemaName + '_' + @pTableName

      BEGIN TRY
          IF ( @pAction = 'ADD'
                OR @pAction = 'Drop' )
            BEGIN
                --Check if Column exists for SourceTable table
                SET @vColumnExists=(SELECT Count(*)
                                    FROM   INFORMATION_SCHEMA.COLUMNS
                                    WHERE  TABLE_SCHEMA = @pSchemaName
                                           AND TABLE_NAME = @pTableName
                                           AND COLUMN_NAME = @pColumnName)

                IF ( @vColumnExists = 0
                     AND @pAction = 'ADD' )
                  BEGIN
                      PRINT ' COLUMN ::' + @pColumnName
                            + ' does not exists for Source Table ::'
                            + @pTableName
                            + ' Please add column to Source Table to include in CDC Table.'
                  END
                ELSE
                  BEGIN
                      PRINT ' COLUMN ::' + @pColumnName
                            + ' exists for Source Table ::' + @pTableName
                            + '-->Proceeding to Next Step.'
                  END

                --Check if Column exists for CDC table
                IF ( @vColumnExists != 0
                     AND @pAction = 'ADD' )
                  SET @vColumnExistsCDC=(SELECT Count(*)
                                         FROM   INFORMATION_SCHEMA.COLUMNS
                                         WHERE  TABLE_SCHEMA = 'cdc'
                                                AND TABLE_NAME = @pSchemaName + '_' + @pTableName + '_CT'
                                                AND COLUMN_NAME = @pColumnName)

                IF ( @vColumnExistsCDC != 0
                     AND @pAction = 'ADD' )
                  BEGIN
                      PRINT ' COLUMN ::' + @pColumnName
                            + ' is already part of CDC Tble ::cdc.'
                            + @pSchemaName + '_' + @pTableName + 'CT'
                  END

                IF ( @vColumnExistsCDC = 0
                     AND @pAction = 'ADD' )
                  BEGIN
                      PRINT ' COLUMN ::' + @pColumnName
                            + ' is not part of CDC Tble ::cdc.'
                            + @pSchemaName + '_' + @pTableName
                            + 'CT -->Proceeding to ADD this column'
                  END

                --COPY EXISTING CDC TABLE TO TEMP TABLE
                --Drop Temp table before Creating/Loading IT
                SET @vDropCreateTemp=' IF Object_id(N''tempdb..'
                                     + @vTempTableName
                                     + ''') IS NOT NULL
                    BEGIN
                        DROP TABLE ##'
                                     + @pSchemaName + '_' + @pTableName + ' END'

                PRINT @vDropCreateTemp

                EXEC (@vDropCreateTemp)

                SET @vSQLTempTable= 'SELECT * INTO ' + @vTempTableName
                                    + ' From cdc.' + @pSchemaName + '_' + @pTableName
                                    + '_CT'

                PRINT @vSQLTempTable

                EXEC(@vSQLTempTable)

                IF ( @vColumnExistsCDC = 0
                     AND @pAction = 'ADD' )
                  BEGIN
                      --ADD COLUMN TO TEMP TABLE
                      SET @vDataType=(SELECT CASE
                                               WHEN DATA_TYPE IN ( 'CHAR', 'varchar', 'nvarchar' ) THEN DATA_TYPE + '('
                                                                                                        + Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50))
                                                                                                        + ')'
                                               WHEN Data_Type IN ( 'int', 'bigint', 'smallint', 'tinyint',
                                                                   'money', 'bit', 'date', 'datetime' ) THEN Data_Type
                                               WHEN data_type IN ( 'numeric', 'decimal' ) THEN DATA_TYPE + '('
                                                                                               + Cast(Numeric_Precision_Radix AS VARCHAR(50))
                                                                                               + ',' + Cast(Numeric_scale AS VARCHAR(50))
                                                                                               + ')'
                                             END AS DataType
                                      FROM   INFORMATION_SCHEMA.COLUMNS
                                      WHERE  TABLE_NAME = @pTableName
                                             AND COLUMN_NAME = @pColumnName)
                      SET @vSQLAlterTable='ALTER TABLE ' + @vTempTableName + ' ADD '
                                          + @pColumnName + ' ' + @vDataType

                      PRINT @vSQLAlterTable

                      EXEC (@vSQLAlterTable)

                      -- ENABLE CDC ON TABLE ( INCLUDING NEW COLUMN)
                      IF Object_id(N'tempdb..##ColumnList') IS NOT NULL
                        BEGIN
                            DROP TABLE ##ColumnList
                        END

                      CREATE TABLE ##ColumnList
                        (
                           ColumnList NVARCHAR(2000)
                        )

                      SET @vGetAlreadyExistingColumns= N' DECLARE @vCDCAlreadyEnabledColumns NVARCHAR(2000)
                          SELECT @vCDCAlreadyEnabledColumns = COALESCE(@vCDCAlreadyEnabledColumns+ '','', '''')
                          + QUOTENAME(COLUMN_NAME) FROM  INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='''
                                                       + @pSchemaName + '_' + @pTableName + '_'
                                                       + 'CT'' AND COLUMN_NAME NOT
                                                   IN (''__$start_lsn'',
                                                   ''__$end_lsn'',
                                                   ''__$seqval'',
                                                   ''__$operation'',
                                                   ''__$update_mask'')
                                                   PRINT @vCDCAlreadyEnabledColumns
                                                   Insert into ##ColumnList values (@vCDCAlreadyEnabledColumns)'

                      PRINT @vGetAlreadyExistingColumns

                      EXEC ( @vGetAlreadyExistingColumns)

                      SELECT @vColumnList = ColumnList + ',[' + @pColumnName + ']'
                      FROM   ##ColumnList

                      PRINT @vColumnList

                      -- DISABLE CDC ON SOURCE TABLE
                      SET @vDisableCDC='EXEC sys.sp_cdc_disable_table @source_schema='''
                                       + @pSchemaName + ''',
         @source_name='''
                                       + @pTableName + ''',@capture_instance='''
                                       + @pSchemaName + '_' + @pTableName + ''''

                      PRINT @vDisableCDC

                      EXEC (@vDisableCDC)

                      --Enable CDC
                      SET @vEnableCDC='EXEC sys.sp_cdc_enable_table
         @source_schema=''' + @pSchemaName
                                      + ''',@source_name=''' + @pTableName
                                      + ''', @role_name=NULL, @captured_column_list= '''
                                      + @vColumnList + ''''

                      PRINT @vEnableCDC

                      EXEC (@vEnableCDC)

                      -- INSERT RECORD FROM Temp to CDC Table
                      SET @vLoadCDCTable=' INSERT INTO cdc.' + @pSchemaName + '_'
                                         + @pTableName
                                         + '_CT
                                      SELECT * FROM '
                                         + @vTempTableName + ''

                      --  Drop Table '+@vTempTableName+''
                      PRINT @vLoadCDCTable

                      EXEC (@vLoadCDCTable)
                  END

            /***-------------------------------------DROP COLUMN LOGIC STARTS-----------------------------************/
                --Build Column List excluding Drop column
                IF EXISTS (SELECT 1
                           FROM   INFORMATION_SCHEMA.COLUMNS
                           WHERE  TABLE_SCHEMA = 'cdc'
                                  AND TABLE_NAME = @pSchemaName + '_' + @pTableName + '_CT'
                                  AND COLUMN_NAME = @pColumnName)
                   AND @pAction = 'Drop'
                  BEGIN
                      SELECT @vDropColumnList = Stuff(o.COLUMNNAME, 1, 1, '')
                      FROM   INFORMATION_SCHEMA.COLUMNS t
                             CROSS APPLY (SELECT ',' + Column_Name + Char(10) AS [text()]
                                          FROM   INFORMATION_SCHEMA.COLUMNS c
                                          WHERE  c.Table_Name = t.Table_Name
                                                 AND c.COLUMN_NAME <> @pColumnName
                                          FOR XML PATH('')) o (COLUMNNAME)
                      WHERE  t.Table_Name = 'dbo_' + @pTableName + '_CT'

                      --Get Columns without Sytem Columns 
                      SELECT @vDropColumnListExcSys = Stuff(o.COLUMNNAME, 1, 1, '')
                      FROM   INFORMATION_SCHEMA.COLUMNS t
                             CROSS APPLY (SELECT ',' + Column_Name + Char(10) AS [text()]
                                          FROM   INFORMATION_SCHEMA.COLUMNS c
                                          WHERE  c.Table_Name = t.Table_Name
                                                 AND c.COLUMN_NAME <> @pColumnName
                                                 AND C.COLUMN_NAME NOT IN ( '__$start_lsn', '__$end_lsn', '__$seqval', '__$operation', '__$update_mask' )
                                          FOR XML PATH('')) o (COLUMNNAME)
                      WHERE  t.Table_Name = 'dbo_' + @pTableName + '_CT'

                      -- DISABLE CDC for Drop Column
                      SET @vDisableCDC='EXEC sys.sp_cdc_disable_table @source_schema='''
                                       + @pSchemaName + ''',
         @source_name='''
                                       + @pTableName + ''',@capture_instance='''
                                       + @pSchemaName + '_' + @pTableName + ''''

                      EXEC (@vDisableCDC)

                      -- ENABLE TABLE EXCLUDING GIVEN COLUMN
                      SET @vEnableCDC='EXEC sys.sp_cdc_enable_table
         @source_schema=''' + @pSchemaName
                                      + ''',@source_name=''' + @pTableName
                                      + ''', @role_name=NULL, @captured_column_list= '''
                                      + @vDropColumnListExcSys + ''''

                      EXEC (@vEnableCDC)

                      --COPY DATA FROM TEMP DATA TO CDC TABLE
                      SET @vLoadCDCTable=' INSERT INTO cdc.' + @pSchemaName + '_'
                                         + @pTableName + '_CT(' + @vDropColumnList
                                         + ')
                                      SELECT '
                                         + @vDropColumnList + ' FROM ' + @vTempTableName
                                         + ''

                      --Drop Table '+@vTempTableName+''
                      EXEC (@vLoadCDCTable)
                  END
            END
          ELSE
            PRINT ' ADD OR Drop are the correct actions available for this Procedure.Please provide ADD or Drop for SP Signature'
      END TRY

      BEGIN CATCH
          ROLLBACK

          PRINT ' ERROR Occured and All Transactions are rolledback.'
      END CATCH
  END

How to Create History OR Audit Views from Change Data Capture ( CDC) Tables in SQL Server - SQL Server Tutorial

Scenario:

You are working as SQL Server DBA or developer, you have enabled Change Data Capture on one of the SQL Server Database, After enabling CDC you  want to create AUDIT OR HIST Views
so  can get the changed records. 

Solution:

The below script will create view for each of the table on which CDC is enabled in your database.
I have used Audit Schema, before you run the script, You need to create Audit Schema or If you want to create views in Hist Schema then change Schema in Below Code from Audit To Hist.

Each view will return us below columns 
  • Commit_Time,
  • Colum_Name,
  • Old_Value,
  • New_Value,
  • ModifiedBy




IF OBJECT_ID(N'tempdb..##CDC_TableList') IS NOT NULL
BEGIN
    DROP TABLE ##CDC_TableList
END

SELECT T.name AS TableName,
       C.name AS ColumnName,
       'CASE WHEN (sys.Fn_cdc_is_bit_set (sys.Fn_cdc_get_column_ordinal (''dbo_'
       + T.name + ''',''' + C.name+
       + '''),__$update_mask) = 1) THEN Cast('
       + C.Name
       + ' AS SQL_VARIANT) ELSE NULL END AS '
       + C.name  AS CaseStm INTO ##CDC_TableList
 FROM   sys.tables T
       INNER JOIN sys.columns c
               ON T.OBJECT_ID = c.OBJECT_ID
WHERE  is_tracked_by_cdc = 1
       AND c.is_identity = 0
       AND C.name NOT IN ( 'CreatedBy', 'CreatedDate', 'ModifiedBy', 'ModifiedDate' )
       AND T.TYPE='U'
       AND T.is_ms_shipped<>1
      
      -- Select * from sys.tables

DECLARE @TableName NVARCHAR(200)
DECLARE cdc_cursor CURSOR FOR 
SELECT DISTINCT TableName FROM ##CDC_TableList
OPEN cdc_cursor 
FETCH NEXT FROM cdc_cursor INTO @TableName

WHILE @@FETCH_STATUS = 
BEGIN

DECLARE @ColumnList  NVARCHAR(MAX)
DECLARE @CaseStmList NVARCHAR(MAX)
DECLARE @SQL_Stm  NVARCHAR(MAX)
DECLARE @SQL_Stm_DropView  NVARCHAR(MAX)
PRINT @TableName

 SELECT @ColumnList = STUFF(o.COLUMNNAME, 1, 1, '')
 FROM ##CDC_TableList t CROSS APPLY  (SELECT ',' + ColumnName+CHAR(10) AS [text()]
            FROM  ##CDC_TableList c
            WHERE    c.TableName = t.TableName
            FOR XML PATH('')) o (COLUMNNAME)
 WHERE t.TableName=@TableName
 PRINT @ColumnList
 
 SELECT @CaseStmList = STUFF(o.COLUMNNAME, 1, 1, '')
 FROM ##CDC_TableList t CROSS APPLY  (SELECT ',' + CaseStm+CHAR(10) AS [text()]
            FROM  ##CDC_TableList c
            WHERE    c.TableName = t.TableName
            FOR XML PATH('')) o (COLUMNNAME)
 WHERE t.TableName=@TableName
 --PRINT @CaseStmList


--If using Other Schema than AUDIT THEN change to that.
SET @SQL_Stm=' CREATE VIEW AUDIT.vw_'+@TableName+' AS

SELECT sys.Fn_cdc_map_lsn_to_time(up_b.__$start_lsn) AS Commit_Time, up_b.Column_Name, up_b.Old_Value, up_a.New_Value,up_a.ModifiedBy
FROM
    ( SELECT __$start_lsn, column_name, old_value,ModifiedBy
   FROM (SELECT __$start_lsn,'+@CaseStmList+',ModifiedBy FROM cdc.fn_cdc_get_all_changes_dbo_'+@TableName+'( sys.fn_cdc_get_min_lsn(''dbo_'+@TableName+'''), sys.fn_cdc_map_time_to_lsn(''largest less than or equal'',GETDATE()), N''all update old'')
 WHERE __$operation = 3
  ) AS BeforeUpdate
  UNPIVOT (old_value FOR column_name IN ('+@ColumnList+') ) AS unp) AS up_b INNER JOIN
  (SELECT __$start_lsn, column_name, new_value,ModifiedBy
   FROM (SELECT __$start_lsn,'+@CaseStmList+',ModifiedBy FROM cdc.fn_cdc_get_all_changes_dbo_'+@TableName+'(sys.fn_cdc_get_min_lsn(''dbo_'+@TableName+'''), sys.fn_cdc_map_time_to_lsn(''largest less than or equal'',GETDATE()), N''all'')
    WHERE __$operation = 4
   ) AS AfterUpdate
   UNPIVOT (new_value FOR column_name IN ('+@ColumnList+') ) AS unp ) AS up_a
ON up_b.__$start_lsn = up_a.__$start_lsn AND up_b.column_name = up_a.column_name'
--Drop View if already exists
SET @SQL_Stm_DropView= 'IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
         WHERE TABLE_NAME = ''vw_'+@TableName+''')
   DROP VIEW AUDIT.vw_'+@TableName
   PRINT @SQL_Stm_DropView

EXEC (@SQL_Stm_DropView)
EXEC ( @SQL_Stm)
FETCH NEXT FROM cdc_cursor INTO @TableName
END 

CLOSE cdc_cursor 
DEALLOCATE cdc_cursor

How to Enable Change Data Capture (CDC) On Entire Table OR Enable CDC On Table With List Of Columns in SQL Server

Scenario: 

Sometime we have requirement to Enable CDC on Table including all the columns OR enable CDC on table with set of columns. The SP can perform both tasks depending upon provided parameters.This Stored Procedure will be used when CDC is already not enabled on Table. If Change Data Capture (CDC) is already enabled on Table , The Stored Procedure will not perform any action.

Solution: 

The below stored Procedure can be used to enable CDC on all the columns of a table or any chosen columns.



USE [DatabaseName]
GO
/*-----------------------------------------------------------------------------------------------------
How to Execute: EXEC usp_EnableCdcOnTableWithOrWithoutColumnList @pSchemaName,@pTableName,@pColumnList

Enable CDC on Table with All columns
Example :usp_EnableCdcOnTableWithOrWithoutColumnList 'SchemaName','TableName',NULL

Enable CDC on Table with Given columns
Example :usp_EnableCdcOnTableWithOrWithoutColumnList 'SchemaName','TableName','Col1,Col2'
---------------------------------------------------------------------------------------------------------*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Usp_enablecdcontablewithorwithoutcolumnlist] 
@pSchemaName VARCHAR(50),--> Provide the Schema Name where table exists
@pTableName  VARCHAR(100),--> TableName to ENABLE CDC ON.
@pColumnList VARCHAR(1000)--> ColumnList eg.'col1,col2'
AS
  BEGIN
      --DECLARE LOCAL VARIABLES

      DECLARE @vSQLStatment NVARCHAR(MAX)
      DECLARE @vSQLEnableCDC NVARCHAR(MAX)
      DECLARE @vXML XML;
      DECLARE @vCDCEnableInd INT
      DECLARE @vColumnCount INT

      -- IF CDC ALREADY ENABLED

      SET @vCDCEnableInd=(SELECT is_tracked_by_cdc
                          FROM   sys.tables
                          WHERE  name = @pTableName)

      -- CHECK IF CORRECT TABLE NAME IS PROVIDED

      IF NOT EXISTS (SELECT 1
                     FROM   INFORMATION_SCHEMA.COLUMNS
                     WHERE  TABLE_NAME = @pTableName)
        BEGIN
            PRINT ' The given table does not exists in Database::'
                  + @pTableName
        END
      -- ENABLE CDC WITH ALL THE COLUMNS IF NO COLUMNS PROVIDED

      ELSE
        BEGIN
            IF @pColumnList IS NULL
               AND @vCDCEnableInd = 1
              BEGIN
                  PRINT 'CDC is alrady enabled on ::' + @pTableName
                      
              END

            IF @pColumnList IS NULL
               AND @vCDCEnableInd = 0
              BEGIN
                  SELECT @vSQLStatment = 'EXEC sys.sp_cdc_enable_table
      @source_schema = ''' + @pSchemaName
                                         + ''',
      @source_name   = ''' + @pTableName
                                         + ''',
      @role_name     = null;'

                  EXEC sp_executesql
                    @vSQLStatment

                  PRINT ' CDC Enabled on ::' + @pTableName
                        + ' for all the columns'
              END

            -- IF COLUMN LIST IS PROVIDED FOR CDC

            IF ( LEN(@pColumnList) > 0 )
              BEGIN
                  SET @vXML=CAST('' + REPLACE(@pColumnList, ',', '')
                                 + '
' AS XML);
                  -- CHECK IF Incorrect COLUMN NAME IS PROVIDEDED

                  SELECT @vColumnCount = COUNT(*)
                  FROM   @vXML.nodes('/a') AS R(nref)
                  WHERE  NOT EXISTS (SELECT 1
                                     FROM   INFORMATION_SCHEMA.COLUMNS I
                                     WHERE  I.TABLE_NAME = @pTableName
                                     AND I.COLUMN_NAME = nref.value('.', 'nvarchar(50)'))

                  IF ( @vColumnCount <> 0 )
                   PRINT ' The List of Columns provided do not exit in Source Table.Check the ColumnList'
                  -- IF All columns Exists in Source Table, Enable CDC

                  IF ( @vColumnCount = 0
                       AND @vCDCEnableInd = 1 )
                    BEGIN
                        PRINT ' The CDC is already Enabled for this table.'
                    END

                  IF ( @vColumnCount = 0
                       AND @vCDCEnableInd = 0 )
                    BEGIN
                        --Enable CDC

                        SET @vSQLEnableCDC='EXEC sys.sp_cdc_enable_table
         @source_schema=''' + @pSchemaName
                                           + ''',@source_name=''' + @pTableName
                                           + ''', @role_name=NULL, @captured_column_list= '''
                                           + @pColumnList + ''''

                        EXEC (@vSQLEnableCDC)

                        PRINT ' CDC Enabled on ::' + @pTableName + ' for '
                              + @pColumnList + ' Columns.'
                    END
              END
        END
  END