How to: Script Out Unique Constraint creation script
I have a list of Unique Constraints which creation scripts I would need.
Is there an easy way to do this? (something like querying sp_help, sys.objects,..)
Or would I need to do this manually for every constraint ( on 30+ tables)?
Workaround I am using at the moment:
- Object Explorer
- ‘script constraint as’
- ‘CREATE to’
2 Solutions collect form web for “How to: Script Out Unique Constraint creation script”
You can try something like this:
SELECT ColumnName = c.name, TableName = t.name, CreateCmd = 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + ' ADD CONSTRAINT ' + i.name + ' UNIQUE(' + c.name + ')' FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id INNER JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id INNER JOIN sys.tables t ON c.object_id = t.object_id WHERE is_unique_constraint = 1
This gives you a listing of columns and tables where a unique constraint is applied to, and it gives you the T-SQL command to newly create that
Ever used Powershell?
$null = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo"); $svr = New-Object Microsoft.SqlServer.Management.Smo.Server("YourServerInstance"); $svr.Databases["YourDatabase"].Tables.Indexes.Script();
Indexes are collections you can iterate over.