比如 在我们导入sqlserver数据到oracle时 时常碰到列超出长度的问题 很是烦劳,报错是又不说明是那一列 所以我们只有自己去查
一列列的写sql语句麻烦死了 如下可以查询一张表中所有列的最大长度
go
declare @lengh int;
declare @i int;
declare @tableName varchar(20);
DECLARE @SQLString NVARCHAR(500)
declare @a table (a int,b varchar(100)) ;
set @tableName='B2B_CONSTRACT';
set @lengh=(select count(*) from syscolumns where object_name(id)=@tableName);
insert into @a select ROW_NUMBER() OVER (ORDER BY name),name from syscolumns where object_name(id)=@tableName;
set @i=1;
while @i<=@lengh
begin
SET @SQLString = N'select max(len('+(select b from @a where a=@i)+')) as '+(select b from @a where a=@i)+' from '+@tableName;
exec (@SQLString);
set @i=@i+1;
end;