Dropping constraints (SQL Server 2k8)

For dropping constraints on selected table:

Code:

$PROMPT(type,Constraint type?,,,% PK F UQ TR C D,All "Primary Key" "Foreign Key" "Unique Key" Trigger Check Default)$
$$
DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql +
CASE
WHEN con.[type] = 'TR' THEN 'DROP TRIGGER '
ELSE 'ALTER TABLE $OBJECT$ DROP CONSTRAINT '
END
+ con.[name] + CHAR(10) + 'GO' + CHAR(10)
FROM
sys.schemas s
INNER JOIN
sys.tables t
ON t.[schema_id] = s.[schema_id]
INNER JOIN
sys.objects con
ON con.parent_object_id = t.[object_id]
WHERE con.[type] LIKE '$type$'
AND t.[object_id] = OBJECT_ID('$OBJECT$')

SELECT @sql
$$

For dropping foreign key constrains from tables referencing the selected table:

Code:

$$
DECLARE @sql NVARCHAR(MAX) = ''

SELECT
@sql += 'ALTER TABLE ' + QUOTENAME(ss.name) + '.' + QUOTENAME(st.name) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + CHAR(10) + 'GO' + CHAR(10)
FROM
sys.tables AS st
INNER JOIN
sys.foreign_keys AS fk
   ON fk.parent_object_id = st.[object_id]
INNER JOIN
sys.tables AS tt
   ON tt.[object_id] = fk.referenced_object_id
INNER JOIN
sys.schemas AS ss
   ON ss.[schema_id] = tt.[schema_id]

WHERE 1 = 1 AND tt.[object_id] = OBJECT_ID('$OBJECT$')

SELECT @sql
$$

Using the INFORMATION_SCHEMA it should bit a bit simpler. Perhaps someday...

Great tip. Thank you! It would be great if SQL Assistant can execute the resulting script immediately without inserting generated code into the editor. Perhaps we can implement that snippet processing option someday.

	SysOp wrote:
	Great tip. Thank you! It would be great if SQL Assistant can execute the resulting script immediately without inserting generated code into the editor. Perhaps we can implement that snippet processing option someday.

It can. You only have to remove @sql declaration and assignment and change Action from "Insert Output Into Code" to "Display Output Results" and you're ready to go. I have a couple of snippets working that way, few of them are quite nasty, I'll post theme here later (need some cosmetics). But this one actually drops objects, I'd like to see what it is going to do before 'my will be done' :) Since it works on every object of the selected type I might not want everything to be removed.

I'm glad you like it, thank you very much.