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?
Add a comment
|
4 Answers
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
Comments
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.
1 Comment
Rocky Singh
Can you let me know the exact script for generating create script for all constraints?
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.
3 Comments
Rocky Singh
Can you let me know the exact step to generate only constraints and not table script?
Fourth
tools > options > sql server object explorer > turn off everything except constraints > generate them > turn everything back on
Rocky Singh
With the option memtioned in the screen it is generating create table scripts too, I want the script of constraints only.
You can program something like this using SMO or even easier is to just use the script wizard in SSMS. Go to your database in the Object Explorer and right-click on it. Select Tasks->Generate Scripts (NOT "Script Database as"). In the wizard you can make sure that constraints are scripted and remove other items. Check the generated script and make sure that it does what you need. If not, go through the wizard again making the necessary adjustments.
