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

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



Joined: 11 Mar 2010
Posts: 1390

Post Concerning constraints (SQL Server 2k8) Reply with quote
Showing constraints in SA message window:
Code:

$PROMPT(type,Constraint type?,,Parameter for View Constraints macro,% PK F UQ TR C D,All "Primary Key" "Foreign Key" "Unique Key" Trigger Check Default)$

$$
SELECT    
   s.[schema_id]
   ,s.[name] AS [schema_name]
    ,t.[object_id] AS table_id
    ,t.[name] AS table_name
    ,con.[object_id] AS constraint_id
    ,con.[name] AS constraint_name
    ,con.[type]
    ,con.type_desc

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$')
$$


Creating a drop script for constraints:
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    
   + 'ALTER TABLE $OBJECT$ DROP CONSTRAINT ' + 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
$$


Another one for disabling/dropping triggers:
Code:

$PROMPT(dtrig,Disable or Drop?,,,DISABLE ENABLE DROP )$
$$
DECLARE @sql NVARCHAR(MAX) = ''

SELECT
   @sql += 'ALTER TABLE $OBJECT$ '
   + CASE '$dtrig$'
        WHEN 'DISABLE' THEN 'DISABLE TRIGGER '
        WHEN 'ENABLE' THEN 'ENABLE TRIGGER '
        WHEN 'DROP' THEN 'DROP CONSTRAINT '
     END
   + QUOTENAME( 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] = 'TR'
   AND   t.[object_id] = OBJECT_ID( '$OBJECT$' )
SELECT @sql
$$

Mon Jul 11, 2011 4:09 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.