How to generate all constraints scripts

Question

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?

Answer 1

This can be done easily from SQL Server Management Studio.

Right-click on the database and go to Tasks, Generate Scripts....

This will bring up a script generation wizard that will generate DROP and CREATE scripts for whatever schema constructs that you select.

Select your database

Make sure that you select Script Drop to True

Select Tables

Select New Query Editor Window and click Finish.

Answer 2

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

Answer 3

This will get you all the constraints in the database, you can filter it by what you need:

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

I think you might also need to look at any indices, statistics, etc. that might also keep you from dropping the column.