SQLServer資料庫表欄位超長，找到超長欄位腳本

平時開發系統時偶爾會遇到數據超長導致往資料庫中保存時出錯。

使用下邊的腳本可以方便的找出超長的欄位。

1.通過正式表創建臨時表，修改臨時表中varchar、nvarchar的長度為max

declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='TableName';--正式表表名：此處需要修改
set @temp_table_name = @table_name+'_temp';--臨時表表名：此處需要修改

--根據正式表創建臨時表
set @sql = 'select * into '+@temp_table_name+' from '+@table_name +' where 1<>1;';
exec(@sql);

--修改varchar/nvarchar臨時表欄位長度為max
set @sql = '';
select @sql=@sql+('alter table '+@temp_table_name+' alter column '+b.name+' '+c.name+'(max);')  
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@temp_table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

exec(@sql);

--手動往臨時表中寫入數據

2.數據手動寫入臨時表後，查找超長欄位

declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='TableName';--正式表表名：此處需要修改
set @temp_table_name = @table_name+'_temp';--臨時表表名：此處需要修改
--校驗臨時表是哪個欄位超長
create table #col_tab
(
    id int,
    col_name varchar(100),
    col_condition varchar(500)
);

insert into #col_tab(id,col_name,col_condition)
select ROW_NUMBER() over(order by b.colid) id,
b.name,
(case c.name when 'nvarchar' then 'len' when 'varchar' then 'datalength' end)+
'('+b.name+')>'+cast((case c.name when 'nvarchar' then b.length/2 when 'varchar' then b.length end) as varchar) 
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

select * from #col_tab ;

declare @cnt int ;
select @cnt = COUNT(*) from #col_tab;

declare @index int;
declare @col_condition varchar(500);
declare @col_name varchar(100);

set @index=1;
while @index<=@cnt
begin
    select @col_condition = col_condition,@col_name=col_name from #col_tab where id = @index;
    set @sql = 'declare @condition_cnt int;';
    set @sql = @sql+'select @condition_cnt=COUNT(*) from '+@temp_table_name+' where '+@col_condition+';';
    --set @sql = @sql+'print @condition_cnt;';
    set @sql = @sql+'if(@condition_cnt>0)
    begin
        print ''['+@col_name+']欄位超長!'';
    end;';
exec(@sql);
    set @index=@index+1;
end;

drop table #col_tab;

 3.新建測試表

CREATE TABLE [dbo].[USERS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](30) NULL,
    [password] [varchar](30) NULL,
    [roleid] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

表截圖如下：

修改表名，運行 1.通過正式表創建臨時表，修改臨時表中varchar、nvarchar的長度為max 腳本

declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='USERS';--正式表表名：此處需要修改
set @temp_table_name = @table_name+'_temp';--臨時表表名 

--根據正式表創建臨時表
set @sql = 'select * into '+@temp_table_name+' from '+@table_name +' where 1<>1;';
exec(@sql);

--修改varchar/nvarchar臨時表欄位長度為max
set @sql = '';
select @sql=@sql+('alter table '+@temp_table_name+' alter column '+b.name+' '+c.name+'(max);')  
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@temp_table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

exec(@sql);

--手動往臨時表中寫入數據

生成臨時表如下：

可以看出varchar的長度修改為了max.

4.修改表名後運行腳本2

declare @temp_table_name varchar(50);
declare @table_name varchar(50);
declare @sql varchar(max);
set @table_name='USERS';--正式表表名：此處需要修改
set @temp_table_name = @table_name+'_temp';--臨時表表名 
--校驗臨時表是哪個欄位超長
create table #col_tab
(
    id int,
    col_name varchar(100),
    col_condition varchar(500)
);

insert into #col_tab(id,col_name,col_condition)
select ROW_NUMBER() over(order by b.colid) id,
b.name,
(case c.name when 'nvarchar' then 'len' when 'varchar' then 'datalength' end)+
'('+b.name+')>'+cast((case c.name when 'nvarchar' then b.length/2 when 'varchar' then b.length end) as varchar) 
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id and a.name=@table_name 
and a.xtype='U'and b.xusertype=c.xusertype
and c.name in ('varchar','nvarchar')
order by b.colid;

select * from #col_tab ;

declare @cnt int ;
select @cnt = COUNT(*) from #col_tab;

declare @index int;
declare @col_condition varchar(500);
declare @col_name varchar(100);

set @index=1;
while @index<=@cnt
begin
    select @col_condition = col_condition,@col_name=col_name from #col_tab where id = @index;
    set @sql = 'declare @condition_cnt int;';
    set @sql = @sql+'select @condition_cnt=COUNT(*) from '+@temp_table_name+' where '+@col_condition+';';
    --set @sql = @sql+'print @condition_cnt;';
    set @sql = @sql+'if(@condition_cnt>0)
    begin
        print ''['+@col_name+']欄位超長!'';
    end;';
exec(@sql);
    set @index=@index+1;
end;

drop table #col_tab;

生成where條件是關鍵，運行後如下圖：

之後迴圈where條件查找臨時表中數據超長欄位，使用print列印出超長欄位的名字。

此腳本在欄位較多的情況下，排查問題非常方便。