SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Dropping constraints (SQL Server 2k8)

 
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository View previous topic
View next topic
Dropping constraints (SQL Server 2k8)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1396

Post Dropping constraints (SQL Server 2k8) Reply with quote
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...
Fri Aug 19, 2011 3:35 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
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.
Fri Aug 19, 2011 9:52 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1396

Post Reply with quote
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.
Fri Aug 19, 2011 10:00 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.