Changing from Int to Bigint Identifying the tables.

Have you ever wondered if any of the tables are running out the size of the integers. Many times when we are maxing out on the integer values in your integer based columns we need to change it to Bigint to make it work.  The below script is an example on how to do it. It gives the Tables, column name and the max value of the int in that column which helps you to identify the Columns and change them to Bigint.  

Script :

create table ##test_table

(

table_name varchar(50), column_name VARCHAR(50), count1 int

)

insert into ##test_table (table_name,column_name)

SELECT table_name , column_name

FROM information_schema.columns where data_type = 'int'

declare test_cursor cursor

For

SELECT table_name , column_name

FROM ##test_table for update of count1

DECLARE @table_name varchar(50), @column_name VARCHAR(50),@sqlcommand nvarchar(2000),@COUNT1 INT

OPEN test_cursor

FETCH NEXT FROM test_cursor INTO @table_name, @column_name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sqlcommand = 'SELECT @cnt=COUNT('+@column_name+') FROM '+ @table_name

EXECUTE sp_executesql @sqlcommand, N'@table_name nvarchar(50),@column_name nvarchar(50),@cnt int OUTPUT', @table_name = @table_name, @column_name = @column_name, @cnt=@COUNT1 OUTPUT

update ##test_table

set count1 = @COUNT1

where CURRENT of test_cursor

FETCH NEXT FROM test_cursor INTO @table_name, @column_name

END

CLOSE test_cursor

DEALLOCATE test_cursor

select table_name , column_name, count1 as maxintvalue from ##test_table

drop table ##test_table