Maximum of each column value

[lavadan]

I have a small database with 10-12 tables. How can I find maximum value for each column for all tables via a query?

 

[bborissov]

USE YourDatabase

CREATE TABLE #Tmp (sql varchar(max))
EXEC sp_MSforeachtable '
DECLARE @sql varchar(max)
SET @sql = ''SELECT ''''?'''' AS TableName ''
SELECT @sql = @sql + CASE WHEN Data_Type IN (''bit'',''text'') THEN '''' ELSE '', MAX(''+Column_Name+'') AS ''+Column_Name END
FROM Information_Schema.Columns
WHERE QUOTENAME(Table_Name) = SUBSTRING(''?'',CHARINDEX(''['',''?'',3),8000)
SET @sql = @sql+'' FROM ?''
INSERT INTO #Tmp VALUES (@sql)
' 
DECLARE @sql nvarchar(max)
SET @sql = ''
SELECT @sql = @sql + Sql+CHAR(13)+CHAR(10) FROM #Tmp
DROP TABLE #Tmp
EXEC sp_executesql @sql

but WHY?


Borislav Borissov
VFP9 SP2, SQL Server

 

[lavadan]

This does and work and throws an error. I am using sql 2016. I wrote a script using cursor but it takes a long time since i have close to 3000 tables. Any other way to determine this?

 

[bborissov]

And the error is?

Borislav Borissov
VFP9 SP2, SQL Server

 

[gbaughma]

I have a small database with 10-12 tables.

I wrote a script using cursor but it takes a long time since i have close to 3000 tables.

I don't think we're getting all / complete information here.


Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg