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');