从一个单位获得一个SQL SERVER 2000数据库,其中有几十张表,有的表有好几百个字段,其中多数NVARCHAR字段的长度设置成大于510字节,这样这些表的总长度就超过了8060字节,不能再对其进行INSERT或UPDATE操作。而那些NVARCHAR字段所实际使用的长度很小,想找出每个NVARCHAR字段所使用的最大长度,再以此长度为标准将该字段设置成这个长度,这样就减少了表中各元组的总长度,进而可以进行INSERT 或UPDATE操作。
当然可以先用类似:SELECT MAX(LEN(单位名称)) FROM 代发 得到该字段的最大使用长度,再用类似:
ALTER TABLE 代发 ALTER COLUMN 单位名称 NVARCHAR(15)
一个一个地修改,但太麻烦了。
有SQL 高手提供了以下语句:DECLARE @SchemaName sysname,--架构名
@TableName sysname,--表名
@ColumnName sysname,--列名
@Max_LenColumn INT,--列最大长度
@FindColumnMaxLen NVARCHAR(4000),--构造寻找列最大长度字符串
@BatchUpdateSQL NVARCHAR(4000) --构造批量更新SQL字符串,下面初始化游标
SELECT 'dbo' AS SchemaName,tb.name AS TableName,col.Name ColumnName into #all FROM syscolumns col
INNER JOIN sysobjects tb ON col.ID=tb.ID INNER JOIN systypes tp ON col.xusertype=tp.xusertype WhERE col.Xusertype=231 AND tb.xtype='U'
--select * from #all
declare my_cousor cursor for select Schemaname,TableName,ColumnName from #all
OPEN my_cousor
FETCH NEXT FROM my_cousor INTO @SchemaName,@TableName,@ColumnName
SET @BatchUpdateSQL =''--下面开始进入循环体
WHILE @@fetch_status=0
BEGIN --下面开始找出该字段最大长度
SET @FindColumnMaxLen=''
SET @FindColumnMaxLen='SELECT @Max_Len=MAX(LEN('+ @ColumnName + ')) FROM ' + @SchemaName +'.'+@TableName
Exec sp_executesql @FindColumnMaxLen,N'@Max_Len INT OUTPUT',@Max_Len=@Max_LenColumn Output --下面的构造更新字符串
SET @BatchUpdateSQL =@BatchUpdateSQL + 'ALTER TABLE '+ @SchemaName +'.'+ @TableName + ' ALTER COLUMN ' +@ColumnName+' NVARCHAR('+CONVERT(NVARCHAR(510),@Max_LenColumn) +');' +CHAR(13) --下面开始循环下一条
FETCH NEXT FROM my_cousor INTO @SchemaName,@TableName,@ColumnName
END
CLOSE my_cousor--关闭游标
DEALLOCATE my_cousor--执行更新语句
PRINT @BatchUpdateSQL
EXEC sp_executesql @BatchUpdateSQL但其中的:
SET @BatchUpdateSQL =@BatchUpdateSQL + 'ALTER TABLE '+ @SchemaName +'.'+ @TableName + ' ALTER COLUMN ' +@ColumnName+' NVARCHAR('+CONVERT(NVARCHAR(510),@Max_LenColumn) +');' +CHAR(13) 语句仅对510字节长度的NVARCHAR字段进行缩减,没有考虑其原来的长度并不固定的情况。对上面的语言在SQL SERVER 2000的查询分析器中运行,其结果显示下列错误:
警告: 聚合或其它 SET 操作消除了空值。
很多行与上面同样的消息
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。
警告: 聚合或其它 SET 操作消除了空值。
很多行与上面同样的消息
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '!' 附近有语法错误。
警告: 聚合或其它 SET 操作消除了空值。
很多行与上面同样的消息
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '_bm_tsdwb0_' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'A新增_U修改_D删除' 附近有语法错误。
警告: 聚合或其它 SET 操作消除了空值。
很多行与上面同样的消息而且运行后没有对字段进行任何缩减,请教其原因是什么,应该怎么修改,谢谢!
当然可以先用类似:SELECT MAX(LEN(单位名称)) FROM 代发 得到该字段的最大使用长度,再用类似:
ALTER TABLE 代发 ALTER COLUMN 单位名称 NVARCHAR(15)
一个一个地修改,但太麻烦了。
有SQL 高手提供了以下语句:DECLARE @SchemaName sysname,--架构名
@TableName sysname,--表名
@ColumnName sysname,--列名
@Max_LenColumn INT,--列最大长度
@FindColumnMaxLen NVARCHAR(4000),--构造寻找列最大长度字符串
@BatchUpdateSQL NVARCHAR(4000) --构造批量更新SQL字符串,下面初始化游标
SELECT 'dbo' AS SchemaName,tb.name AS TableName,col.Name ColumnName into #all FROM syscolumns col
INNER JOIN sysobjects tb ON col.ID=tb.ID INNER JOIN systypes tp ON col.xusertype=tp.xusertype WhERE col.Xusertype=231 AND tb.xtype='U'
--select * from #all
declare my_cousor cursor for select Schemaname,TableName,ColumnName from #all
OPEN my_cousor
FETCH NEXT FROM my_cousor INTO @SchemaName,@TableName,@ColumnName
SET @BatchUpdateSQL =''--下面开始进入循环体
WHILE @@fetch_status=0
BEGIN --下面开始找出该字段最大长度
SET @FindColumnMaxLen=''
SET @FindColumnMaxLen='SELECT @Max_Len=MAX(LEN('+ @ColumnName + ')) FROM ' + @SchemaName +'.'+@TableName
Exec sp_executesql @FindColumnMaxLen,N'@Max_Len INT OUTPUT',@Max_Len=@Max_LenColumn Output --下面的构造更新字符串
SET @BatchUpdateSQL =@BatchUpdateSQL + 'ALTER TABLE '+ @SchemaName +'.'+ @TableName + ' ALTER COLUMN ' +@ColumnName+' NVARCHAR('+CONVERT(NVARCHAR(510),@Max_LenColumn) +');' +CHAR(13) --下面开始循环下一条
FETCH NEXT FROM my_cousor INTO @SchemaName,@TableName,@ColumnName
END
CLOSE my_cousor--关闭游标
DEALLOCATE my_cousor--执行更新语句
PRINT @BatchUpdateSQL
EXEC sp_executesql @BatchUpdateSQL但其中的:
SET @BatchUpdateSQL =@BatchUpdateSQL + 'ALTER TABLE '+ @SchemaName +'.'+ @TableName + ' ALTER COLUMN ' +@ColumnName+' NVARCHAR('+CONVERT(NVARCHAR(510),@Max_LenColumn) +');' +CHAR(13) 语句仅对510字节长度的NVARCHAR字段进行缩减,没有考虑其原来的长度并不固定的情况。对上面的语言在SQL SERVER 2000的查询分析器中运行,其结果显示下列错误:
警告: 聚合或其它 SET 操作消除了空值。
很多行与上面同样的消息
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。
警告: 聚合或其它 SET 操作消除了空值。
很多行与上面同样的消息
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '!' 附近有语法错误。
警告: 聚合或其它 SET 操作消除了空值。
很多行与上面同样的消息
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '_bm_tsdwb0_' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'A新增_U修改_D删除' 附近有语法错误。
警告: 聚合或其它 SET 操作消除了空值。
很多行与上面同样的消息而且运行后没有对字段进行任何缩减,请教其原因是什么,应该怎么修改,谢谢!
上面的数据库是从ORACEL中导入的SQL SERVER 2000中的,已经没有列约束(如是否非空,是否主外键等)了。再请教chuifengde,怎么个改法?