— =============================================
    — Author: Joe
    — Create date: 2011-11-02
    — Description: 根据“DF表名列名”格式重命名所有默认值约束
    — =============================================
    CREATE PROCEDURE [dbo].[uspRenameAllDFName]
    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    ‘+@tablename+’‘+@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