declare @result_table table(
maxid bigint,
columnname nvarchar(200)
);
declare @column_type nvarchar(45);
declare @column_count bigint;
declare @dsql nvarchar(500);
declare @table_name nvarchar(200);
declare @column_name nvarchar(200);
declare columnCursor cursor local for
SELECT
distinct(SysColumns.name)
FROM sys.columns SysColumns
INNER JOIN sys.objects SysObjects ON SysColumns.[object_id]=SysObjects.[object_id] AND SysObjects.type='U' AND SysObjects.is_ms_shipped=0
INNER JOIN sys.types SysTypes ON SysColumns.user_type_id=SysTypes.user_type_id
LEFT JOIN INFORMATION_SCHEMA.COLUMNS ISC ON ISC.TABLE_NAME = SysObjects.name AND ISC.COLUMN_NAME=SysColumns.name
WHERE systypes.name = 'int' AND SysColumns.name like '%ID' AND SysColumns.name not like '%AID';
open columnCursor;
fetch next from columnCursor into @column_name;
while @@FETCH_STATUS = 0
Begin
if @column_name <> 'id'
begin
set @table_name = substring(@column_name,0,len(@column_name) - 1);
set @dsql = 'select @columncount = MAX(' + @column_name + ') from [' + @table_name +']';
IF EXISTS ( select * from sys.objects where object_id = object_id(@table_name))
begin
exec sp_executesql @dsql,N'@columncount bigint out',@column_count output;
insert into @result_table(maxid,columnname) values(@column_count,@column_name);
end
end
fetch next from columnCursor into @column_name;
end
close columnCursor;
deallocate columnCursor;
select * from @result_table
where maxid is not null and maxid > 100000000;
查找SQL SERVER庫的的INT型字段將要溢出的行
最新推荐文章于 2022-11-16 14:14:17 发布