Verilen iki veritabanı arasında yeni(surum) veritabanındaki kolonların uzunlukları eğer eskiden uzun ise
Değişen kolonların alter scriptini hazırlayan bir cümlecik.
İyi çalışmalar.
declare @myserver varchar(100)
declare @mydb varchar(100)
declare @surumserver varchar(100)
declare @surumdb varchar(100)
set @myserver='server 1 adı'
set @mydb='veritabani 1 adi'
set @surumserver='server 2 adi'
set @surumdb='veritabani 2 adi'
EXEC(' SELECT
sclmns.id,
sclmns.scale,
sclmns.prec,
table_name=so.name,
column_name=sclmns.name,
datatype=systypes.name
into #'+@surumdb+'
FROM ['+@surumserver+'].['+@surumdb+'].[dbo].sysobjects so
JOIN ['+@surumserver+'].['+@surumdb+'].[dbo].syscolumns as sclmns ON so.id = sclmns.id
JOIN systypes ON sclmns.xtype=systypes.xtype
WHERE so.xtype=''U''
and sclmns.xtype in (167,127,106,231,104,56,61,189)
ORDER BY so.name,sclmns.colid;
SELECT
sclmns.id,
sclmns.scale,
sclmns.prec,
table_name=so.name,
column_name=sclmns.name,
datatype=systypes.name,
length=systypes.length into #'+@mydb+'
FROM ['+@myserver+'].['+@mydb+'].[dbo].sysobjects so
JOIN ['+@myserver+'].['+@mydb+'].[dbo].syscolumns as sclmns ON so.id = sclmns.id
JOIN systypes ON sclmns.xtype=systypes.xtype
WHERE so.xtype=''U''
and sclmns.xtype in (167,127,106,231,104,56,61,189)
ORDER BY so.name,sclmns.colid;
select
''alter table ''+sdb.table_name+'' ALTER COLUMN ''+sdb.column_name+'' ''+
case
when sdb.datatype=''bigint'' then ''bigint''
when sdb.datatype=''decimal'' then ''decimal(''+cast(sdb.prec as varchar(20))+'',''+cast(sdb.scale as varchar(20))+'')''
when sdb.datatype=''nvarchar'' then ''nvarchar(''+cast(sdb.prec as varchar(10))+'')''
when sdb.datatype=''bit'' then ''bit''
when sdb.datatype=''int'' then ''int''
when sdb.datatype=''datetime'' then ''datetime''
when sdb.datatype=''timestamp'' then ''timestamp''
end as ''DegisenKolonlar'' into #table
from #'+@mydb +' as mdb
inner join #'+@surumdb+' as sdb on mdb.prec!=sdb.prec and mdb.table_name=sdb.table_name and mdb.column_name=sdb.column_name
where sdb.prec>mdb.prec;
select * from #table
where DegisenKolonlar is not null
');