Sql – How to generate all constraints scripts

sqlsql-serversql-server-2005sql-server-2008

I have a requirement where I have to change collation of my DB for that I need to drop all the constraints and recreate them after running the collation change script at my DB. May I know how can I generatescript of all constraints of my DB?

Best Solution

SELECT top 1 
    'ALTER TABLE '+ SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id) + 
    ' ADD CONSTRAINT ' + dc.name + ' DEFAULT(' + definition 
    + ') FOR ' + c.name
FROM sys.default_constraints dc
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id

script to generate all constraints

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 

get all constraints on db then filter on your table