January 14, 2004 at 7:51 am
Hi everyone,
Is there a way to calculate the MIN and MAX (or any other aggregate value) for all columns in a table and place these results into a summary table without cursoring through a list of columns? I've got a stored proc that takes a table name and gets a list of all columns using the INFORMATION_SCHEMA.COLUMNS view, then generates the MIN and MAX values for each column, one at a time. I'd much rather calculate and update the summary table as a set process instead of the cursor.
Any suggestions?
Thanks!
Mark Dalton
January 14, 2004 at 11:54 am
This will give you the Min and Max for every column without using a cursor. The generation of the SQL statement is lightning fast, but selecting a lot of Min's and Max's is terribly slow.
Declare @SQL varchar(8000),
@TableName sysname
Set @TableName = 'YourTable'
Select @SQL = IsNull(@SQL + ', ', '') + 'Max([' + sc.name + ']) As [Max_' + sc.name + '], Min([' + sc.name + ']) As [Min_' + sc.name + ']'
From syscolumns As sc with(nolock)
Inner Join sysobjects As so with(nolock) on so.id = sc.id
Where so.name = @TableName
And sc.xtype Not In (34, 35, 99)
Set @SQL = 'Select ' + @SQL + char(10) + 'From [' + @TableName + '] with(nolock)'
Exec(@SQL)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply