修改订单号数据类型代码

if exists (select * from tempdb..sysobjects where name ='##T')
drop table ##T
create table ##T (name varchar(128),t varchar(1000),keys varchar(1000),tab varchar(100))

exec sp_MSforeachtable '

insert ##T(name,t,keys)
exec sp_helpindex ''?''
update ##T set tab=''?''
where tab is null
'

select
(CASE WHEN t LIKE '%primary key%'
THEN
'
begin try
ALTER TABLE '+tab+' DROP CONSTRAINT '+quotename(name)+'
end try
begin catch
print  ERROR_MESSAGE()
end catch
'
ELSE
'
begin try
if object_id('''+tab+''') is not null and
 exists(select 1 from sys.indexes
where name ='''+name+''')
drop  index '+quotename(name)+' on '+tab+'
end try
begin catch
print  ERROR_MESSAGE()
end catch
'END) del,
(CASE WHEN t LIKE '%primary key%'
THEN
'ALTER TABLE '+tab+' ADD PRIMARY KEY CLUSTERED
('+
replace(keys,'(-)',' desc ')
+')'else
'
if object_id('''+tab+''') is not null and
not exists(select 1 from sys.indexes
where name ='''+name+''')
create '+
replace(replace(left(t,len(t)-patindex('% on %',t)),',',' '),'noncluster','nonclustered')
+' index '+quotename(name)+' on '+tab+'('+
replace(keys,'(-)',' desc ')
+')
' END) ad

 from ##T

WHERE keys LIKE '%orderid%' OR keys LIKE '%ordercode%'
drop table ##T

 

 

 

 

SELECT *,
tochange='begin try
alter table '+sch+'.['+tbl+'] alter column '+col+' bigint '+(CASE WHEN is_nullable=0 THEN ' not null ' ELSE '' END)+'
print ''OK!   '+sch+'.['+tbl+'].'+col+'-->>bigint''
end try
begin catch
raiserror(''Error!   '+sch+'.['+tbl+'].'+col+'-->>bigint'',11,1,0)
print ERROR_MESSAGE()
end catch',
toback='begin try
alter table '+sch+'.['+tbl+'] alter column '+col+' '+coltype+
(CASE WHEN coltype IN ('varchar','char') THEN '('+CONVERT(VARCHAR,max_length)+')'
WHEN coltype IN ('nvarchar','nchar') THEN '('+CONVERT(VARCHAR,max_length/2)+')'
 ELSE '' END)
+' '+(CASE WHEN is_nullable=0 THEN ' not null ' ELSE '' END)+'
print ''OK!   '+sch+'.['+tbl+'].'+col+'-->>'+coltype+
(CASE WHEN coltype IN ('varchar','char') THEN '('+CONVERT(VARCHAR,max_length)+')'
WHEN coltype IN ('nvarchar','nchar') THEN '('+CONVERT(VARCHAR,max_length/2)+')'
 ELSE '' END)
+'''
end try
begin catch
raiserror(''Error!   '+sch+'.['+tbl+'].'+col+'-->>'+coltype+
(CASE WHEN coltype IN ('varchar','char') THEN '('+CONVERT(VARCHAR,max_length)+')'
WHEN coltype IN ('nvarchar','nchar') THEN '('+CONVERT(VARCHAR,max_length/2)+')'
ELSE '' END)
+''',11,1,0)'+
'
print ERROR_MESSAGE()
end catch'
 FROM
(SELECT  SCHEMA_NAME(b.SCHEMA_ID)sch
,b.name tbl,a.name col,
c.name coltype ,a.max_length,a.is_nullable

 FROM sys.all_columns a INNER JOIN sys.all_objects b
ON a.object_id=b.object_id
AND a.name IN ('orderid','ordercode')
--AND (a.name LIKE '%orderid%' OR a.name  LIKE '%ordercode%') AND  a.name not IN ('orderid','ordercode')
 AND b.type='u'
INNER JOIN sys.types c
ON a.user_type_id=c.user_type_id

)aa
ORDER BY sch,tbl,col