第一步:
--测试字段超长
DECLARE @temp_table_name VARCHAR(50);
DECLARE @table_name VARCHAR(50);
DECLARE @sql VARCHAR(MAX);
SET @table_name = 'Temp'; --正式表表名:此处需要修改
SET @temp_table_name = @table_name + '_temp'; --临时表表名:此处可以修改
--判断临时表是否存在
IF(OBJECT_ID('tempdb..'+@temp_table_name+'')) IS NOT NULL
DROP TABLE [@temp_table_name]
--根据正式表创建临时表
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);
GO
第二步:
--手动往临时表中写入数据
第三步:
GO
--执行下面SQL,查询出具体字段
DECLARE @temp_table_name VARCHAR(50);
DECLARE @table_name VARCHAR(50);
DECLARE @sql VARCHAR(MAX);
SET @table_name = 'Temp'; --正式表表名:此处需要修改
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;
DROP TABLE [@temp_table_name]