Sunday, May 4, 2014

SQL Server - Truncate tables in a SQL Server database

Problem
As a DBA I have found many occasions in testing of various SQL Server deployments and scripts where I need to load a database with data from a lower environment or where I need to alter a table that might already have data in it in such a way that I need to first eliminate the data before I can proceed. Depending on the foreign key constraints in place, clearing old data can be a tedious process.
Solution
When I don't care about existing data in a SQL Server database, but I don't want to resort to dropping and re-creating the database and all the additional tasks that come with it, I have found truncating all the data in all the tables to be an effective solution. I prefer truncating over deleting the data because of the various advantages truncating has by nature when you want to reset to a clean state.
Unfortunately, in most complex database designs there are numerous foreign key relationships that make it impossible to just use a simple "for each" loop and issuing a TRUNCATE command.
To illustrate this problem and my solution, we first need to create a database with several tables with foreign key constraints.

CREATE DATABASE ForeignKeyTest
GO
USE ForeignKeyTest
GO
CREATE TABLE T1
       (
        T1Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,Customer VARCHAR(50) NOT NULL
       )
GO
CREATE TABLE T2
       (
        T2Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,T1Id INT NOT NULL
       ,OrderNum INT NOT NULL
       )
GO
CREATE TABLE T3
       (
        T3Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,T2Id INT NOT NULL
       ,ItemId INT NOT NULL
       ,Qty INT NOT NULL
       )
GO
CREATE TABLE Item
       (
        ItemId INT IDENTITY
                   PRIMARY KEY
                   NOT NULL
       ,Item VARCHAR(50)
       )
GO
ALTER TABLE dbo.T2
ADD CONSTRAINT FK_T2_T1 FOREIGN KEY( T1Id) REFERENCES dbo.T1 ( T1Id)
GO
ALTER TABLE dbo.T3
ADD CONSTRAINT FK_T3_T2 FOREIGN KEY( T2Id) REFERENCES dbo.T2 (T2Id)
GO
ALTER TABLE dbo.T3  
ADD CONSTRAINT FK_T3_Item FOREIGN KEY(ItemId) REFERENCES dbo.Item(ItemId)
GO
INSERT dbo.T1
        ( Customer )
SELECT 'FirstCust'
UNION
SELECT 'SecondCust'
UNION
SELECT 'ThirdCust' ;
INSERT dbo.T2
        ( T1Id, OrderNum )
SELECT 1, 1
UNION
SELECT 1, 2
UNION 
SELECT 2, 3
UNION
SELECT 3, 4
UNION
SELECT 3, 5 ;
INSERT dbo.Item
        ( Item )
SELECT 'Gunk'
UNION
SELECT 'Slop'
UNION
SELECT 'Glop'
UNION
SELECT 'Crud' ;
INSERT dbo.T3
        ( T2Id, ItemId, Qty )
SELECT 1,3,5
UNION
SELECT 1,2,2
UNION
SELECT 2,1,4
UNION
SELECT 3,3,10;
This creates tables that look like this:
Create a database with several tables with foreign key constraints
The data in the tables look like:
T1
Truncate all tables in a database
T2
T3
Just use a simple "for each" loop and issuing a TRUNCATE command.
ItemTable
Unfortunately, in most complex database designs there are numerous foreign key relationships
If you attempt to truncate any of the tables you get this error:
BEGIN TRAN
TRUNCATE TABLE dbo.T2
ROLLBACK
If you attempt to truncate any of the tables you get this error:
If you attempt to delete data from the tables you get this error:
BEGIN TRAN
DELETE dbo.T1
ROLLBACK
If you attempt to delete data from the tables you get this error:
If you only want to use the DELETE statement against your tables, you can disable the constraints, delete your data and re-enable your constraints.
BEGIN TRAN
ALTER TABLE dbo.T1 NOCHECK CONSTRAINT ALL
ALTER TABLE dbo.T2 
NOCHECK CONSTRAINT ALL
DELETE dbo.T1
SELECT * FROM dbo.T1 AS T

ALTER TABLE dbo.T1 CHECK CONSTRAINT ALL
ALTER TABLE dbo.T2 CHECK CONSTRAINT ALL
ROLLBACK
If you only want to use the DELETE statement against your tables, you can disable the constraints, delete your data and re-enable your constraints.
This works, but if you try do the same thing using TRUNCATE you get the following:
BEGIN TRAN
ALTER TABLE dbo.T1 NOCHECK CONSTRAINT ALL
ALTER TABLE dbo.T2 NOCHECK CONSTRAINT ALL
TRUNCATE TABLE  dbo.T1
SELECT * FROM dbo.T1 AS T
ROLLBACK
This works, but if you try do the same thing using TRUNCATE you get the following:

A solution that can TRUNCATE all tables

In order to truncate all tables in your database you must first remove all the foreign key constraints, truncate the tables, and then restore all the constraints.
The below script accomplishes this in an automated fashion, by executing the following steps:
  1. Create a table variable to store the constraint drop and creation scripts for the database
  2. Load the data for all tables in the database
  3. Execute a cursor to drop all constraints
  4. Truncate all tables
  5. Recreate all the constraints
 /* TRUNCATE ALL TABLES IN A DATABASE */
DECLARE @dropAndCreateConstraintsTable TABLE
        (
         DropStmt VARCHAR(MAX)
        ,CreateStmt VARCHAR(MAX)
        )
/* Gather information to drop and then recreate the current foreign key constraints  */
INSERT  @dropAndCreateConstraintsTable
        SELECT  DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
                + '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT ['
                + ForeignKeys.ForeignKeyName + ']; '
               ,CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
                + '].[' + ForeignKeys.ForeignTableName
                + '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName
                + '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn
                + ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id)
                + '].[' + sys.objects.[name] + ']([' + sys.columns.[name]
                + ']); '
        FROM    sys.objects
        INNER JOIN sys.columns
                ON ( sys.columns.[object_id] = sys.objects.[object_id] )
        INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName
                           ,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema
                           ,sys.objects.[name] AS ForeignTableName
                           ,sys.columns.[name] AS ForeignTableColumn
                           ,sys.foreign_keys.referenced_object_id AS referenced_object_id
                           ,sys.foreign_key_columns.referenced_column_id AS referenced_column_id
                     FROM   sys.foreign_keys
                     INNER JOIN sys.foreign_key_columns
                            ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] )
                     INNER JOIN sys.objects
                            ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id )
                     INNER JOIN sys.columns
                            ON ( sys.columns.[object_id] = sys.objects.[object_id] )
                               AND ( sys.columns.column_id = sys.foreign_key_columns.parent_column_id )
                   ) ForeignKeys
                ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
                   AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
        WHERE   ( sys.objects.[type] = 'U' )
                AND ( sys.objects.[name] NOT IN ( 'sysdiagrams' ) )
/* SELECT * FROM @dropAndCreateConstraintsTable AS DACCT  --Test statement*/
DECLARE @DropStatement NVARCHAR(MAX)
DECLARE @RecreateStatement NVARCHAR(MAX)
/* Drop Constraints */
DECLARE Cur1 CURSOR READ_ONLY
FOR
        SELECT  DropStmt
        FROM    @dropAndCreateConstraintsTable
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @DropStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DropStatement
            EXECUTE sp_executesql @DropStatement
            FETCH NEXT FROM Cur1 INTO @DropStatement
      END
CLOSE Cur1
DEALLOCATE Cur1
/* Truncate all tables in the database in the dbo schema */
DECLARE @DeleteTableStatement NVARCHAR(MAX)
DECLARE Cur2 CURSOR READ_ONLY
FOR
        SELECT  'TRUNCATE TABLE [dbo].[' + TABLE_NAME + ']'
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_SCHEMA = 'dbo'
                AND TABLE_TYPE = 'BASE TABLE'
  /* Change your schema appropriately if you don't want to use dbo */
OPEN Cur2
FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DeleteTableStatement
            EXECUTE sp_executesql @DeleteTableStatement
            FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
      END
CLOSE Cur2
DEALLOCATE Cur2
/* Recreate foreign key constraints  */
DECLARE Cur3 CURSOR READ_ONLY
FOR
        SELECT  CreateStmt
        FROM    @dropAndCreateConstraintsTable
OPEN Cur3
FETCH NEXT FROM Cur3 INTO @RecreateStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @RecreateStatement
            EXECUTE sp_executesql @RecreateStatement
            FETCH NEXT FROM Cur3 INTO @RecreateStatement
      END
CLOSE Cur3
DEALLOCATE Cur3
GO   
When the above script is run against any database, all the tables are emptied and reset.

Change the COLLATION of a SQL Server Column

Problem
You've installed SQL Server with all the defaults, created your database and schema then loaded a bunch of data into your database. Now you realize that some or all of the columns in your database needed to have the COLLATION set to be case sensitive. This tip will take you through the steps required to change the column COLLATION of your tables from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1_CS_AS.
Solution

Table Setup

Let's first setup a couple of tables in order to walkthrough these steps required to update the column collation. The following script will create two tables as well as some constraints, indexes and statistics in order to illustrate each of the steps that may be required depending on the design of your schema. For this example we won't add any data to our tables as it wouldn't have any effect on the update process since we are going from case insensitive to case sensitive. It's important to note that if you were performing the reverse update that is from case sensitive to case insensitive, you would also have an extra step of resolving any data issues as the update could result in duplicate data in columns with unique constraints. That said, here is the complete setup script listing.
CREATE TABLE dbo.MainTable
 (PKColumn int NOT NULL IDENTITY (1, 1),
  CharColumn char(10) NULL,
  NCharColumn nchar(10) NULL,
  VarcharColumn varchar(50) NULL,
  NVarcharColumn nvarchar(50) NULL,
  VarcharMaxColumn varchar(MAX) NULL,
  NVarcharMaxColumn nvarchar(MAX) NULL,
  ComputedColumn  AS CharColumn + VarcharColumn)
GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT CK_Table_NVarCharMaxColumn
   CHECK (NVarCharMaxColumn IN ('Apple','Pear','Orange','Banana'))
GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT PK_MainTable PRIMARY KEY CLUSTERED (PKColumn)
GO

CREATE NONCLUSTERED INDEX IX_Table_NVarcharColumn ON dbo.MainTable (NVarcharColumn) 
GO

CREATE STATISTICS Stats_MainTable_VarcharMaxColumn
    ON dbo.MainTable (VarcharMaxColumn) WITH FULLSCAN
GO

CREATE TABLE dbo.FKTable
 (CharColumn char(10) NOT NULL,
  DataColumn int NULL)
GO

ALTER TABLE dbo.FKTable ADD CONSTRAINT PK_FKTable PRIMARY KEY CLUSTERED (CharColumn) 
GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT
 FK_MainTable_FKTable FOREIGN KEY
 (CharColumn) REFERENCES dbo.FKTable
 (CharColumn)
  ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 
GO
Identify columns
Depending on your situation you may need to only change the collation of a few columns or possible every column in your database. You can use the following query which will list any columns in your database that have the default collation.
SELECT t.name "Table Name",
       c.name "Column Name",
       c.collation_name "Collation"
  FROM sys.tables t INNER JOIN
       sys.columns c ON c.object_id=t.object_id INNER JOIN
       sys.types s ON s.user_type_id=c.user_type_id
 WHERE c.collation_name LIKE 'SQL_Latin1_General_CP1_CI_AS'
   AND t.type like 'U'
   AND t.name not like 'spt%'
   AND t.name not like 'MSrep%'
For this tip let's assume that we are changing the collation of every column in our database. If you read the following link, Set or Change the Column Collation, from TechNet you'll notice that there are some restrictions on when you can change the collation of a column. Basically, if a column is referenced by any of the following objects you will not be able to change the collation without first removing these references.
  • A computed column
  • An index
  • Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
  • A CHECK constraint
  • A FOREIGN KEY constraint
Identify referencing objects
Let's now identify which objects need to be removed before we can make the collation update to our columns. The following queries, one for each category listed above, will identify objects that have a dependency on a column that we are going to be updating. Since we are updating all the columns in the database the only filter I have is "WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'". If your requirements are different you can expand on this as required. I've also include a sample output after each query for reference. One other item to note is with the foreign key section. We need to remove the referenced primary key index in order to make the collation update to that column as well so that the section includes two queries. One to identify the foreign key and the other for the primary key.
-- computed columns
SELECT OBJECT_NAME(c.object_id) "Table Name",
       COL_NAME(sd.referenced_major_id, sd.referenced_minor_id) "Column Name",
       c.collation_name "Collation",
       definition "Definition"
  FROM sys.computed_columns cc INNER JOIN
       sys.sql_dependencies sd ON cc.object_id=sd.object_id AND 
                                  cc.column_id=sd.column_id AND 
                                  sd.object_id=sd.referenced_major_id INNER JOIN
       sys.columns c ON c.object_id=sd.referenced_major_id AND 
                        c.column_id = sd.referenced_minor_id
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND sd.class=1

Table NameColumn NameCollationDefinition
MainTableCharColumnSQL_Latin1_General_CP1_CI_AS([CharColumn]+[VarcharColumn])
MainTableVarcharColumnSQL_Latin1_General_CP1_CI_AS([CharColumn]+[VarcharColumn])

--foreign keys
SELECT f.name "Foreign Key Name",
       OBJECT_NAME(f.parent_object_id) "Table Name",
       COL_NAME(fc.parent_object_id,fc.parent_column_id) "Column Name",
       c1.collation_name "Collation",
       OBJECT_NAME (f.referenced_object_id) "Reference Table Name",
       COL_NAME(fc.referenced_object_id,fc.referenced_column_id) "Reference Column Name",
       c2.collation_name "Collation"
  FROM sys.foreign_keys AS f INNER JOIN
       sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN
       sys.columns c1 ON c1.object_id=fc.parent_object_id AND
                         c1.column_id=fc.parent_column_id INNER JOIN
       sys.columns c2 ON c2.object_id=fc.parent_object_id AND
                         c2.column_id=fc.parent_column_id
 WHERE c1.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
    OR c2.collation_name like 'SQL_Latin1_General_CP1_CI_AS'

-- primary keys
SELECT i.name AS "Primary Key Name",
       OBJECT_NAME(ic.object_id) "Table Name",
       COL_NAME(ic.object_id,ic.column_id) "Column Name",
       c.collation_name "Collation"
  FROM sys.indexes AS i INNER JOIN 
       sys.index_columns AS ic ON i.object_id = ic.object_id AND 
                                  i.index_id = ic.index_id INNER JOIN
       sys.columns c ON ic.object_id=c.object_id AND
                        c.column_id=ic.column_id
 WHERE i.is_primary_key=1
   AND c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'

Foreign Key NameTable NameColumn NameCollation
FK_MainTable_FKTableMainTableCharColumnSQL_Latin1_General_CP1_CI_AS
Reference Table NameReference Column NameCollation
FKTableCharColumnSQL_Latin1_General_CP1_CI_AS

Primary Key NameTable NameColumn NameCollation
PK_FKTableFKTableCharColumnSQL_Latin1_General_CP1_CI_AS

--indexes
SELECT i.name AS "Index Name",
       OBJECT_NAME(ic.object_id) "Table Name",
       COL_NAME(ic.object_id,ic.column_id) "Column Name",
       c.collation_name "Collation"
  FROM sys.indexes AS i INNER JOIN 
       sys.index_columns AS ic ON i.object_id = ic.object_id AND 
                                  i.index_id = ic.index_id INNER JOIN
       sys.columns c ON ic.object_id=c.object_id AND
                        c.column_id=ic.column_id
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND i.is_primary_key <> 1
   AND OBJECT_NAME(ic.object_id) NOT LIKE 'sys%'

Index NameTable NameColumn NameCollation
IX_Table_NVarcharColumnMainTableNVarcharColumnSQL_Latin1_General_CP1_CI_AS

-- statistics
SELECT s.name "Statistics Name",
       OBJECT_Name(c.object_id) "Table Name",
       COL_NAME(c.object_id,c.column_id) "Column Name",
    c.collation_name "Collation"
  FROM sys.stats s INNER JOIN
       sys.stats_columns sc on s.stats_id=sc.stats_id AND 
                               s.object_id=sc.object_id INNER JOIN
       sys.columns c ON c.object_id=sc.object_id AND 
                        c.column_id=sc.column_id 
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND s.user_created=1
   AND OBJECT_NAME(c.object_id) NOT LIKE 'sys%'
   AND OBJECT_NAME(c.object_id) NOT LIKE 'MSrep%'

Statistics NameTable NameColumn NameCollation
Stats_MainTable_VarcharMaxColumnMainTableVarcharMaxColumnSQL_Latin1_General_CP1_CI_AS

-- check constraints
SELECT OBJECT_NAME(cc.object_id) "Constraint Name",
       OBJECT_Name(c.object_id) "Table Name",
       COL_NAME(sd.referenced_major_id, sd.referenced_minor_id) "Column Name",
       c.collation_name "Collation",
       definition "Definition"
  FROM sys.check_constraints cc INNER JOIN
       sys.sql_dependencies sd ON cc.object_id=sd.object_id INNER JOIN
       sys.columns c ON c.object_id=sd.referenced_major_id AND 
                        c.column_id = sd.referenced_minor_id
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND cc.type = 'C'
   AND sd.class=1

Constraint NameTable NameColumn NameCollation
CK_Table_NVarCharMaxColumnMainTableNVarcharMaxColumnSQL_Latin1_General_CP1_CI_AS
Definition
([NVarCharMaxColumn]='Banana' OR [NVarCharMaxColumn]='Orange' OR [NVarCharMaxColumn]='Pear' OR [NVarCharMaxColumn]='Apple')

Drop referencing objects

Now that we've identified everything that we need to remove we can use any tool, including SSMS, to create drop scripts for these objects. Remember to at the same time create a script that will recreate these objects so we can run that after our column definition update is done. Here is the script to drop the objects identified above.
ALTER TABLE dbo.MainTable DROP COLUMN ComputedColumn
ALTER TABLE dbo.MainTable DROP CONSTRAINT FK_MainTable_FKTable
ALTER TABLE dbo.FKTable DROP CONSTRAINT PK_FKTable
DROP INDEX dbo.MainTable.IX_Table_NVarcharColumn
DROP STATISTICS dbo.MainTable.Stats_MainTable_VarcharMaxColumn
ALTER TABLE dbo.MainTable DROP CONSTRAINT CK_Table_NVarCharMaxColumn

Update the collation

After all that work we can now issue simple ALTER TABLE ... ALTER COLUMN ... statements to update the collation for each column in our database. Here is the script to perform this operation.
ALTER TABLE dbo.MainTable
 ALTER COLUMN NCharColumn nchar(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN CharColumn char(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.FKTable
 ALTER COLUMN CharColumn char(10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN VarcharColumn varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN NVarcharColumn nvarchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN VarcharMaxColumn varchar(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable 
 ALTER COLUMN NVarcharMaxColumn nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

Recreate referencing objects

Once the schema update completes we can run the script we generated above to recreate all of the referencing objects that we dropped earlier. Here is the script for our example.
ALTER TABLE dbo.MainTable ADD ComputedColumn AS CharColumn + VarcharColumn
GO
ALTER TABLE dbo.MainTable ADD CONSTRAINT CK_Table_NVarCharMaxColumn
   CHECK (NVarCharMaxColumn IN ('Apple','Pear','Orange','Banana'))
GO
CREATE NONCLUSTERED INDEX IX_Table_NVarcharColumn ON dbo.MainTable (NVarcharColumn) 
GO
CREATE STATISTICS Stats_MainTable_VarcharMaxColumn
    ON dbo.MainTable (VarcharMaxColumn) WITH FULLSCAN
GO
ALTER TABLE dbo.FKTable ADD CONSTRAINT PK_FKTable PRIMARY KEY CLUSTERED (CharColumn) 
GO
ALTER TABLE dbo.MainTable ADD CONSTRAINT
 FK_MainTable_FKTable FOREIGN KEY
 (CharColumn) REFERENCES dbo.FKTable
 (CharColumn)
  ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 
GO