Technical Article

Find maximum values for all "int" columns

,

/********************************************************
"CheckAllInts"

Find all columns in all user tables in a database
defined as "int", and get the maximum value for the
column.

Uses a cursor and logs the results to a table. (One-time
CREATE TABLE statement at top of script).
Successive runs over an appropriate period of time would
allow one to find how quickly an integer value is
increasing. Useful for finding columns which may overflow
the boundaries of the integer datatype.

Author: Troy Ketsdever, Silver Creek Consulting
Date: 3/23/2005

*********************************************************/

/********************************************************
  "CheckAllInts"

  Find all columns in all user tables in a database 
  defined as "int", and get the maximum value for the 
  column.

  Uses a cursor and logs the results to a table. (One-time
  CREATE TABLE statement at top of script).
  Successive runs over an appropriate period of time would 
  allow one to find how quickly an integer value is 
  increasing. Useful for finding columns which may overflow 
  the boundaries of the integer datatype.

  Author: Troy Ketsdever, Silver Creek Consulting
  Date:   3/23/2005

*********************************************************/
/*
--- Setup: Need this table to log changes in integer values over time
CREATE TABLE dbo.IntegerLog(
  TABLE_NAME sysname NOT NULL,
  COLUMN_NAME sysname NOT NULL,
  SAMPLE_DATE datetime NOT NULL,
  CONSTRAINT pk_IntegerLog
    PRIMARY KEY CLUSTERED(TABLE_NAME, COLUMN_NAME, SAMPLE_DATE),
  VALUE int NOT NULL
)
*/
SET DEADLOCK_PRIORITY LOW
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE CheckAllInts 
CURSOR FAST_FORWARD
FOR
SELECT 'INSERT INTO dbo.IntegerLog(TABLE_NAME, COLUMN_NAME, SAMPLE_DATE, VALUE) ' +
'SELECT ''' + c.TABLE_NAME + ''', ''' + 
c.COLUMN_NAME + 
''', @SampleDate,  ' +
'COALESCE(MAX(' + QUOTENAME(c.COLUMN_NAME) + '), 0) FROM ' + QUOTENAME(c.TABLE_NAME) + ' (NOLOCK)'
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
  AND c.DATA_TYPE = 'int'
FOR READ ONLY

DECLARE @SQL nvarchar(4000)
DECLARE @SampleDate datetime
SET @SampleDate = getdate()

OPEN CheckAllInts

FETCH NEXT FROM CheckAllInts INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executeSQL @stmt = @SQL, @params = N'@SampleDate datetime', @SampleDate = @SampleDate
FETCH NEXT FROM CheckAllInts INTO @SQL
END

CLOSE CheckAllInts
DEALLOCATE CheckAllInts

SELECT * 
FROM IntegerLog 
ORDER BY VALUE DESC

-- CLEANUP
-- DROP TABLE dbo.IntegerLog

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating