Finding integer database columns that may have their data type changed to reduce size
The following stored procedure will help identify those columns. It starts by creating a temporary table that stores the minimum and maximum values that each integer type can hold and then runs a dynamic query to find the minimum and maximum value for every table / column that defines one of those integer types. Once the min/max values have been determined it looks up the smallest type that can hold the value current values and gives a result such as the following:

table_name      column_name         current_type    min_val max_val proposed_type   proposed_min    proposed_max    space_saved
RideLegVetting  RideNumber          int             1       21      tinyint         0               255             2025654
RideLegVetting  LegNumber           int             0       99      tinyint         0               255             2025654


For each column it shows the curent type and min/max values found in the table along with a new proposed type and the min/max value it can hold. Based on the cardinality it also indicates an estimate of the amount of space that will be saved. The stored procecedure is as follows:

CREATE PROCEDURE OptimizeIntSizes AS
BEGIN
    SET NOCOUNT ON
    CREATE TABLE #DataTypeRanges
    (
        name nvarchar(128),
        size tinyint,
        low bigint,
        high bigint
    )
    INSERT INTO #DataTypeRanges VALUES (N'tinyint', 1, 0, 255)
    INSERT INTO #DataTypeRanges VALUES (N'smallint', 2, -32768, 32767)
    INSERT INTO #DataTypeRanges VALUES (N'int', 4, -2147483648, 2147483647)
    INSERT INTO #DataTypeRanges VALUES (N'bigint', 8, -9223372036854775808, 9223372036854775807) 
    CREATE TABLE #Results
    (
        table_name nvarchar(128),
        column_name nvarchar(128),
        current_type nvarchar(128),
        min_val bigint,
        max_val bigint,
        proposed_type nvarchar(128),
        proposed_min bigint,
        proposed_max bigint,
        space_saved bigint
    )
    DECLARE @table_name nvarchar(128)
    DECLARE @column_name nvarchar(128)
    DECLARE @current_type nvarchar(128)
    DECLARE @proposed_type nvarchar(128)
    DECLARE @low bigint
    DECLARE @high bigint
    DECLARE @size tinyint
    DECLARE @cardinality bigint
    DECLARE @min_val bigint
    DECLARE @max_val bigint
    DECLARE @proposed_min bigint
    DECLARE @proposed_max bigint
    DECLARE @proposed_size tinyint
    DECLARE @space_saved bigint
    DECLARE @sql nvarchar(max)
    DECLARE @params nvarchar(max)
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE FindCursor CURSOR STATIC FOR
        SELECT tables.name AS table_name, cols.name AS column_name, types.name, dtr.size, dtr.low, dtr.high
        FROM sys.columns cols
            JOIN sys.tables tables ON tables.object_id = cols.object_id
            JOIN sys.types types ON types.system_type_id = cols.system_type_id
            JOIN #DataTypeRanges dtr ON dtr.name COLLATE DATABASE_DEFAULT = types.name COLLATE DATABASE_DEFAULT
    OPEN FindCursor
    FETCH FindCursor INTO @table_name, @column_name, @current_type, @size, @low, @high
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = N'SELECT @cardinality_out = COUNT(1), @min_val_out = MIN([' + @column_name + ']), @max_val_out = MAX([' + @column_name + ']) FROM [' + @table_name + ']'
        SET @params = N'@cardinality_out bigint OUTPUT, @min_val_out bigint OUTPUT, @max_val_out bigint OUTPUT'
        EXECUTE sp_executesql @sql, @params, @cardinality_out = @cardinality OUTPUT, @min_val_out = @min_val OUTPUT, @max_val_out = @max_val OUTPUT
        SELECT TOP 1 @proposed_type = name, @proposed_size = size, @proposed_min = low, @proposed_max = high
            FROM #DataTypeRanges
            WHERE @min_val &gt;= low  AND @max_val &lt;= high
            ORDER BY size
        IF @proposed_type &lt;&gt; @current_type AND @max_val IS NOT NULL
        BEGIN
            SET @space_saved = (@size - @proposed_size) * @cardinality
            INSERT INTO #Results VALUES (@table_name, @column_name, @current_type, @min_val, @max_val, @proposed_type, @proposed_min, @proposed_max, @space_saved)
        END
        FETCH FindCursor INTO @table_name, @column_name, @current_type, @size, @low, @high
    END
    CLOSE FindCursor
    DEALLOCATE FindCursor
    SELECT * FROM #Results ORDER BY space_saved DESC
END