question

champ avatar image
champ asked

change default connstraint name

i want to make a SINGLE script which change the default constraint name like change DF__ProdPosSt__PosBu__5518DB2E to DF_TABLE NAME_COLUMN NAME and we can use this script for more than one table
tsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
This is no small piece of work and unless someone has already created this script and is generous enough to share it here you may well have to work on creating your own or get a contractor in to do the work for you. There are so many variations on what might need to be handled in writing something like this that it would be too big a job for a forum to tackle in one go. If you make a start on creating your own and come against any individual problems then bring those back here and we will try to help you with those.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
I wouldn't even bother trying to do this with TSQL. It's too big a pain in the bottom. Go to PowerShell. You'll have a much easier time parsing the strings, using table names, all that stuff.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

champ avatar image champ commented ·
ya you r right its really a long script
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I agree with @Fatherjack - you're asking for a rather complex script without making any suggestions or attempts yourself. I can't give you a tested, known-to-work script, but this is a starter that I came up with from the top of my head: DECLARE @s NVARCHAR(max) SET @s='' SELECT @s = @s + 'ALTER TABLE ' + schema_name(schema_id) + '.' + object_name(parent_object_id) + ' DROP CONSTRAINT ' + dc.name + ' GO ALTER TABLE ' + schema_name(schema_id) + '.' + object_name(parent_object_id) + ' ADD CONSTRAINT ' + 'DF_' + schema_name(schema_id) + '_' + object_name(parent_object_id) + '_' + c.name + ' DEFAULT ' + definition + ' FOR ' + c.name + ' GO ' 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 PRINT @s --EXEC sp_executesql @s No warranties...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
+1 I wouldn't even have tried. Well done.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
I have a script from a naming convention implementation from work last year. Instead of dropping the defaults like @Magnus suggested, you can use `sp_rename` to do the job: SELECT 'EXEC sp_rename N'''+QUOTENAME(DC.name,'[')+''','+' N''['+'DF_'+t.name+'_'+c.name+']'', N''OBJECT'';' FROM sys.tables t INNER JOIN sys.default_constraints DC ON t.object_id = dc.parent_object_id INNER JOIN sys.columns C ON t.object_id=c.object_id AND c.column_id=dc.parent_column_id This will return the names as expected, you copy the output and run it.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
+1 Another person better & braver than me. Excellent!
1 Like 1 ·
WilliamD avatar image WilliamD commented ·
Dunno about better and braver, just idle! Powershell really should be the tool of choice when mucking around with this sort of thing, but I had the code to hand and it does the job.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.