List Constraints For A Column

[Ambsgirl]

How do I list all the constriants that exist on one column? sp_helpconstraint will list all constraints for the table as does select * from sysobjects where objectproperty(id,N'IsConstraint')=1 and
parent_obj=coalesce(object_id('di_menus'),'-1').

I'm trying to delete a column programmatically and the constraints on that column could vary per db so it needs to be dynamic.

Thanks!

 

[ESquared]

Yet again... it matters what order things are created and dropped in. Forget the previous view.

CREATE PROCEDURE RecreateConstraints
AS
CREATE TABLE #Constraints (
   Seq int identity(1, 1) NOT NULL,
   [Drop] varchar(1000),
   [Create] varchar(1000)
)

INSERT #Constraints ([Drop], [Create])
SELECT
   [Drop] = Convert(varchar(1000), 'ALTER TABLE ' + QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name) + ' DROP CONSTRAINT ' + QuoteName(C.Constraint_Name)),
   [Create] =
      Convert(varchar(1000),
         'ALTER TABLE ' + QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name) + ' ADD CONSTRAINT ' + QuoteName(C.Constraint_Name)
         + ' ' + C.Constraint_Type
          + CASE C.Constraint_Type
         WHEN 'CHECK' THEN ' ' + CC.Check_Clause
         WHEN 'DEFAULT' THEN ' ' + DEFAULT_CLAUSE + ' FOR ' + QuoteName(C.Column_Name)
         WHEN 'FOREIGN KEY' THEN ' (' + K.Column_Names + ') REFERENCES ' + QuoteName(FK.Table_Schema) + '.' + QuoteName(FK.Table_Name) + ' (' + FKK.Column_Names + ')'
            + CASE WHEN R.Update_Rule <> 'NO ACTION' THEN ' ON UPDATE ' + R.Update_Rule ELSE '' END
            + CASE WHEN R.Delete_Rule <> 'NO ACTION' THEN ' ON DELETE ' + R.Delete_Rule ELSE '' END
         WHEN 'PRIMARY KEY' THEN ' '
            + CASE IndexProperty(Object_Id(QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name)), C.Constraint_Name, 'IsClustered') WHEN 1 THEN '' ELSE 'NON' END + 'CLUSTERED (' + K.Column_Names + ')'
            + CASE WHEN I.OrigFillFactor <> 0 THEN ' WITH FILLFACTOR = ' + Convert(varchar(11), I.OrigFillFactor) ELSE '' END
            + CASE WHEN G.GroupName <> 'PRIMARY' THEN ' ON ' + QuoteName(G.GroupName) ELSE '' END
         WHEN 'UNIQUE' THEN ' (' + K.Column_Names + ')'
         ELSE ''
         END
      )
FROM
   (
      SELECT Table_Schema, Table_Name, Constraint_Name, Constraint_Type = 'DEFAULT', Column_Name, Default_Clause FROM ColumnDefaults
      UNION ALL SELECT Table_Schema, Table_Name, Constraint_Name, Constraint_Type, NULL, NULL FROM Information_Schema.Table_Constraints
   ) C
   LEFT JOIN dbo.KeyColumnUsage K ON K.Constraint_Name = C.Constraint_Name
   LEFT JOIN Information_Schema.Referential_Constraints R ON K.Constraint_Name = R.Constraint_Name
   LEFT JOIN Information_Schema.Table_Constraints FK ON R.Unique_Constraint_Name = FK.Constraint_Name
   LEFT JOIN dbo.KeyColumnUsage FKK ON R.Unique_Constraint_Name = FKK.Constraint_Name
   LEFT JOIN Information_Schema.Check_Constraints CC ON C.Constraint_Name = CC.Constraint_Name
   LEFT JOIN SysIndexes I ON C.Constraint_Name = I.[Name]
   LEFT JOIN SysFileGroups G ON I.GroupID = G.GroupID
WHERE
   C.Table_Name <> 'dtproperties'
[b]   AND ObjectProperty(Object_ID(C.Table_Name), 'IsTable') = 1 -- Exclude PKs of row-returning functions[/b]
ORDER BY
   CASE C.Constraint_Type
   WHEN 'CHECK' THEN 1
   WHEN 'DEFAULT' THEN 2
   WHEN 'FOREIGN KEY' THEN 3
   WHEN 'PRIMARY KEY' THEN 4
   WHEN 'UNIQUE' THEN 5
   ELSE ''
   END

SELECT [Drop] FROM #Constraints ORDER BY Seq
SELECT [Create] FROM #Constraints ORDER BY Seq DESC

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]