Sql – T-SQL query to summarise numerical fields in an SQL Server 2000 db

sql-server, tsql

I have an SQL Server 2000 db, and I would like to retrieve summary information for all the numerical fields contained in the user tables of the database.

I can retrieve the names, datatypes and sizes with the following query:

SELECT  t.name AS [TABLE Name],c.name AS [COLUMN Name],p.name AS [DATA Type],p.length AS [SIZE]FROM dbo.sysobjects AS tJOIN dbo.syscolumns AS cON t.id=c.idJOIN dbo.systypes AS pON c.xtype=p.xtypeWHERE t.xtype='U'and p.prec is not null

How can I go one step further, and also list the average value contained within each field?

Can this be done with a subquery, or do I have to place the result of this query in a cursor and loop through a second select query for each column?

Best Solution

My best and quick guess is by using a cursor:

DECLARE @Table varchar(80)DECLARE @Column varchar(80)DECLARE @Sql varchar(300)DECLARE fields CURSOR FORWARD_ONLYFOR SELECT t.name AS [Table], c.name AS [COLUMN]FROM dbo.sysobjects AS tJOIN dbo.syscolumns AS c ON t.id=c.idJOIN dbo.systypes AS p ON c.xtype=p.xtypeWHERE t.xtype='U'  and p.name = 'int'  and p.prec is not nullOPEN fieldsFETCH NEXT FROM fields INTO @Table, @ColumnWHILE @@FETCH_STATUS = 0BEGIN    SET @sql = 'SELECT ''' + @Table + '.' + @Column + ''', Avg(' + @Column+ ') FROM ' + @Table    EXEC(@Sql)    FETCH NEXT FROM fields     INTO @Table, @ColumnENDCLOSE fieldsDEALLOCATE fields

A temporary table should work fine, too. You can also add the Min() and Max() values.Hope it helps