34,594
社区成员
发帖
与我相关
我的任务
分享
DECLARE @D_DatabaseName sysname
DECLARE @sql nvarchar(4000)
DECLARE @S_DatabaseName sysname
set @S_DatabaseName='源数据库'
set @D_DatabaseName='目标数据库'
CREATE TABLE #DF(DFName sysname,TName sysname,FName sysname,DText nvarchar(400))
declare @DFName sysname
declare @TName sysname
declare @FName sysname
declare @DText nvarchar(400)
set @sql = 'insert #DF
select distinct d.name as DFName,b.Name as TName,a.name as FName,c.text as DText
from '+QUOTENAME(@S_DatabaseName)+'.dbo.syscolumns a
join '+QUOTENAME(@S_DatabaseName)+'.dbo.sysobjects b on a.id=b.id
join '+QUOTENAME(@S_DatabaseName)+'.dbo.syscomments c on a.cdefault=c.id
join '+QUOTENAME(@S_DatabaseName)+'.dbo.sysobjects d on c.id=d.id
where d.type=''D'' and b.xtype=''U'' and d.name like ''DF%'''
print @sql
EXEC sp_executesql @sql
select * from #DF
declare df_Cur cursor for
select DFName,TName,FName,DText from #DF
open df_Cur
fetch df_Cur into @DFName,@TName,@FName,@DText
WHILE @@FETCH_STATUS=0
BEGIN
set @sql = N'USE '+QUOTENAME(@D_DatabaseName)+N'
if exists (select 1 from '+QUOTENAME(@D_DatabaseName)+'.dbo.syscolumns a join '+QUOTENAME(@D_DatabaseName)+'.dbo.sysobjects b on a.id=b.id where a.name='''+ @FName+''' and b.name='''+ @TName+''' ) and not exists (select 1
from '+QUOTENAME(@D_DatabaseName)+'.dbo.syscolumns a
join '+QUOTENAME(@D_DatabaseName)+'.dbo.sysobjects b on a.id=b.id
join '+QUOTENAME(@D_DatabaseName)+'.dbo.syscomments c on a.cdefault=c.id
join '+QUOTENAME(@D_DatabaseName)+'.dbo.sysobjects d on c.id=d.id
where b.name='''+ @TName+''' and a.name ='''+ @FName +''')
alter table dbo.'+ @TName +' ADD CONSTRAINT '+ @DFName +' default '+ @DText +' for '+ @FName +'
'
print @sql
EXEC sp_executesql @sql
fetch df_Cur into @DFName,@TName,@FName,@DText
END
CLOSE df_Cur
DEALLOCATE df_Cur
drop table #DF
SELECT a.Name,b.Name,d.Name FROM sysobjects a
INNER JOIN sysobjects b
ON a.parent_obj=b.id
INNER JOIN syscomments c
ON c.id=a.id
INNER JOIN syscolumns d
ON d.colid=c.colid AND d.id=b.id
WHERE a.type='D'
select a.name,b.Name from dbo.sysobjects a inner join dbo.sysobjects b
on a.parent_obj=b.ID
where a.type='D'