How do I query the database for all types of constraints such as Primary Key,Foreign Key, Unique Key, and Default Constraint, and rename its system generate name to a name in following format:
SELECT tbl.name TableName, col.name ColName, ck.name ConstraintName, ck.definition ConstraintDefinition ,' exec sp_rename [' +ck.name + '] , [DF_' + col.name + ']' SqlQuery FROM sys.default_constraints ck INNER JOIN sys.tables tbl on ck.parent_object_id = tbl.object_id INNER JOIN sys.columns col on tbl.object_id = col.object_id and ck.parent_column_id = col.column_id This is for default constraints
use sys.key_constraints ans sys.foreign_keys
The following code is a little more detailed and it too is specific to default constraints.
select quotename(s.name) + '.' + quotename(o.name) as table_name, dc.name as default_name, 'DF_' + o.name + '_' + c.name as new_default_name, 'execute sp_rename ''' + quotename(s.name) + '.' + dc.name + ''', ''' + 'DF_' + o.name + '_' + c.name + ''' , ''OBJECT'' -- ' + s.name + '.' + o.name + '' as rename_script from sys.default_constraints as dc join sys.objects as o join sys.schemas as s on o.schema_id = s.schema_id on o.object_id = dc.parent_object_id join sys.columns as c on o.object_id = c.object_id and c.column_id = dc.parent_column_id where dc.name like 'DF[_][_]%[_][_]%[_][_]%' -- DF__AlertType__Creat__10F7245D and o.type = 'u' and o.is_ms_shipped = 0 order by s.name, o.name 