Finding Max values of column for all the tables for SQL Server
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 is_identity = 1 –and WHERE i.is_primary_key = 1
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
order by TableName
–drop table #Results
Leave a Reply