C#nedir?com Homepage
Forum Home Forum Home > Veritabanları > SQL Server
  New Posts New Posts RSS Feed - otomatik kolon için alter script hazırlama
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

otomatik kolon için alter script hazırlama

 Post Reply Post Reply
   

Author
Message
unalaygun View Drop Down
Newbie
Newbie


Joined: 16 Mart 2008
Location: Turkey
Status: Offline
Points: 17
Post Options Post Options   Thanks (0) Thanks(0)   Quote unalaygun Quote  Post ReplyReply Direct Link To This Post Topic: otomatik kolon için alter script hazırlama
    Posted: 20 Ekim 2008 at 13:00
< ="Content-" content="text/; charset=utf-8">< name="ProgId" content="Word.">< name="Generator" content="Microsoft Word 12">< name="Originator" content="Microsoft Word 12"><>

Verilen iki veritabanı arasında yeni  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

');















Her Nefis Ölümü Tadacaktır.
Back to Top
 Post Reply Post Reply
  Share Topic   
   


Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.01
Copyright ©2001-2014 Web Wiz Ltd.