How can I find all table constraints with a c# script?

Question

I'm in the midst of writing a script that is updating the collation of all varchar columns for every table in my database (Sql Server 2008). I need a way to collect a list of each constraint in a table so that I can drop it, alter the columns the constraint affects, and then re-add the constraint. I know that I am able to grab UK constraints via indexes but what about Default constraints?

Update: I would also like to add that I am trying to do this by looping through the schema. I know I can run a SQL String against the database to collect this information but is there a way to get this information via the GetSchema() or ReportSchema() method?

Update: I would also consider using SMO as a way to accomplish this.

Answer 1

This script returns all default constraints on a table in a SQL Server 2008 database. It gives the name and definition of the constraints on Person.Address table in Adventure Works database. You can substitute the table name with any other table to get the constraints for that table.

SELECT [name],
       [definition]
FROM   sys.default_constraints
WHERE  parent_object_id =
      (SELECT OBJECT_ID FROM sys.tables
       WHERE name = 'Address')

Answer 2

Check out this script to drop and re-create constraints.

http://www.sqlmusings.com/2008/10/04/script-to-droprecreate-check-constraints/

You could also use SMO to loop through each table in a database and drop its constraints.