按命名规范重命名所有默认值约束名称

  今天把用PD设计好的一堆表，生成SQL脚本，在数据库上做生成测试。发现所有默认值约束，居然全部不符合命名规范。

  没有在PD当中找到解决问题的方法，只好在数据库端想办法，想了一下写一个SP,经测试能解决我的问题。

  特记录如下：

  

-- =============================================
-- Author:        Joe
-- Create date: 2011-11-02
-- Description:    根据“DF_表名_列名”格式重命名所有默认值约束
-- =============================================
CREATE PROCEDURE [dbo].[usp_RenameAllDFName]
AS
BEGIN

 SET NOCOUNT ON;
 select A.name as df_name,OBJECT_NAME(A.parent_object_id)as table_name,
 B.name as col_name
 into #tmp
 from sys.default_constraints AS A
 inner join sys.columns AS B
 on A.object_id=B.default_object_id and B.column_id=A.parent_column_id
 
 declare @df_name varchar(50),@table_name varchar(50),@col_name varchar(50)
 declare  csr_df cursor
 for select df_name,table_name,col_name from #tmp
 open csr_df
 fetch next from csr_df
 into @df_name,@table_name,@col_name
 while @@FETCH_STATUS=0
 begin
  EXEC ('exec sp_rename @objname = '''+@df_name+''', @newname = ''DF_'+@table_name+'_'+@col_name+''', @objtype = N''OBJECT''')
 fetch next from csr_df into  @df_name,@table_name,@col_name
 end
 close csr_df
 deallocate csr_df
 drop table #tmp
END