Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to Find Max and Min Values for all the columns for all the tables in SQL Server Database
-- 20220129 From TechBrothersIT https://www.techbrothersit.com/2016/03/how-to-find-max-and-min-values-for-all.html
USE [YourDBName];
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 SchemaName,
''' + @TableName + ''' AS TableName,
''' + @ColumnName + ''' AS ColumnName,
''' + @DataType + ''' AS DataType,
(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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment