use Jiepei_WMS;
|
|
--ÅäÖÃÐÅÏ¢
|
declare @table varchar(200),@column varchar(200),@newcolumn varchar(200),@objname varchar(500),@tpstr varchar(300);
|
|
|
--ÐÞ¸Ä×Ö¶ÎÃû
|
set @table='Department';
|
set @column='Sorter';
|
set @newcolumn='SortById';
|
set @tpstr='int not null';--²»ÄÜÐÞ¸ÄĬÈÏÖµ
|
|
--ɾ³ý¸Ã×Ö¶ÎË÷Òý
|
declare @indexname varchar(200);
|
select @indexname=IDX.Name from sys.indexes IDX
|
inner join sys.index_columns IDXC on IDX.[object_id]=IDXC.[object_id] and IDX.index_id=IDXC.index_id
|
inner join sys.objects O on O.[object_id]=IDX.[object_id]
|
inner join sys.columns C on O.[object_id]=C.[object_id] and O.type='U' and O.is_ms_shipped=0 and IDXC.Column_id=C.Column_id
|
where O.Name=@table and C.Name=@column;
|
if @indexname is not null exec('drop index ['+@table+'].'+@indexname);
|
|
--ÐÞ¸Ä×Ö¶ÎÃû¡¢Ä¬ÈÏÖµ¼°ÀàÐÍ
|
if exists(select 1 from syscolumns where id=(select [object_id] as tblID from sys.all_objects where [type]='U' and [name]<>'dtproperties' and [name]=@table) and name=@column) begin
|
set @objname=@table+'.'+@column;
|
exec sp_rename @objname,@newcolumn;
|
exec('alter table ['+@table+'] ALTER column ['+@newcolumn+'] '+@tpstr);
|
end
|
|
--»¹Ô¸Ã×Ö¶ÎË÷Òý,Ìî³äÒò×ÓΪ80%
|
if @indexname is not null
|
exec('create nonclustered index IDX_'+@table+'_'+@newcolumn+' on ['+@table+'](['+@newcolumn+']) with fillfactor=80');
|
