How to Find Max and Min Values for all the columns for all the tables in SQL Server Database - SQL Server / T-SQL Tutorial

Scenario: Download Script

You are working as SQL Server Developer or T-SQL developer on one of the Project that involve data analysis. You got this requirement in which you need to get the max and min value for each of the column in all the tables in a SQL Server Database. You will be finding max and min values for columns of data types integer, big integer, small int, tiny int, float,decimal,numeric and real.

Solution:

We will be using Cursor to loop through all tables for each of the column to find max and min value. We will save the record in temp table for each column and show at the end of query. We will get Database Name, Schema Name, Table Name, Column Name, Column Data Type, Max Value and Min Value.

USE YourDBName
GO

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)
DECLARE @DataType VARCHAR(50)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,ColumnDataType VARCHAR(50)
    ,MaxValue VARCHAR(50)
    ,MinValue VARCHAR(50)
    )

DECLARE Cur CURSOR
FOR
SELECT DB_Name() AS DatabaseName
    ,s.[name] AS SchemaName
    ,t.[name] AS TableName
    ,c.[name] AS ColumnName
    ,'[' + DB_Name() + ']' + '.[' + s.NAME + '].' + '[' + T.NAME + ']' AS FullQualifiedTableName
    ,d.[name] AS DataType
FROM sys.schemas s
INNER JOIN sys.tables t ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types d ON c.user_type_id = d.user_type_id
WHERE d.NAME LIKE '%int%'
    OR d.NAME LIKE '%float%'
    OR d.NAME LIKE '%decimal%'
    OR d.NAME LIKE '%numeric%'
    OR d.NAME LIKE '%real%'
    OR d.NAME LIKE '%money%'
    AND is_identity = 0

OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName
    ,@DataType

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL

    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' + @SchemaName + ''' AS TableName,
      ''' + @TableName + ''' AS SchemaName,
      ''' + @ColumnName + ''' AS ColumnName,
      ''' + @DataType + ''' AS ColumnName,
      (Select MAX(' + @ColumnName + ') from ' + @FullyQualifiedTableName + ' with (nolock)) 
      AS MaxValue,
      (Select MIN(' + @ColumnName + ') from ' + @FullyQualifiedTableName + ' with (nolock)) 
      AS MaxValue'

    PRINT @SQL

    INSERT INTO #Results
    EXEC (@SQL)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
        ,@DataType
END

CLOSE Cur

DEALLOCATE Cur

SELECT *
FROM #Results
    --drop table #Results

I executed above code on one of my database and here are my results for max and min values for each of the column for all the tables in SQL Server Database.

 How to find Max and Min values for all the columns dynamically in All Tables in SQL Server Database - SQL server Tutorial / T-SQL Tutorial

Check out our other posts on Data Analysis / Data Validation / Data Cleansing

• How to Search in all Columns for all tables in a database for Date Value in SQL Server
• How to Find Percentage of Null Values in every Column of all the Tables in SQL Server Database 
• How to Find Percentage of Empty or Blank Values in every Column of all the Tables in SQL Server Database
• How to Get Row Count Of All The Tables In SQL Server Database
• How to search for a String in all Columns in all tables in SQL Server Database
• How to find and Replace Text in all the columns in all the tables in SQL server Database
• How to find values with Leading or Trailing Spaces in SQL Server Table 
• How to Trim Leading and Trailing spaces for all the values in all the Columns in all the Tables in a SQL Server Database
• How to Find Duplicate Records in All the Tables in SQL Server Database
• How to Find & Delete Duplicate Records on All Tables in SQL Server Database
• How to Generate Select all Columns with or Without Top X Rows From All Tables Query Dynamically in SQL Server
• Concatenate SQL Server Columns for each Table Query Dynamically in SQL Server 
• How to List all Missing Identity Values for all Tables in SQL Server Database