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 😉

One thought on “Drop all table constraints per (x)type

  1. Pingback: How to drop all Stored Procedures? « Clint Huijbers's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s