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
 

2 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Cryptocurrency creation technologies went further and decided to create their own app for buying cryptocurrency and bitcoins. If you are looking for where you can safely exchange variptocurrencies, I suggest you check out https://hexn.io. I'm buying cryptocurrency there now, it's profitable and fast. I know a lot of people didn't like this app, but I like it. There are reviews that you can read.

    ReplyDelete