Drop all table constraints per (x)type

I found an inspiring stored procedure and rewrote it to drop all (kind of) contraints.

sp_drop_constraints will drop all constraints on the specified table, including CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and DEFAULT constraints. Compile it in your master database and use it from any user database.

The code:

Download it here: here.

This SP uses my MultiValue function (included in the code above).

Here’s a list of all the possible values for the xtype column in the sysobjects table of a SQL Server database:

AF = Aggregate function (CLR)
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = In-lined table-function
IT = Internal table
P = Stored procedure
PC = Assembly (CLR) stored-procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = Table function
TR = SQL DML Trigger
TT = Table type
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

How to use:

NOTE: Don’t forget to generate a script to recreate them 😉

Performance Comparison between Data Type Conversion Techniques in SSIS 2008

Try to use Transact-SQL conversion in your SSIS packages when you have several million rows!
Why? Just read this article! 🙂

The results (30 million rows):

Baseline = 35 sec
Transact-SQL conversion = 46 sec
Conversion on the OLE DB Source component = 45 sec
Data Conversion transformation = 49 sec
Derived Column transformation = 71 sec