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

Question

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 t
JOIN dbo.syscolumns AS c
ON t.id=c.id
JOIN dbo.systypes AS p
ON c.xtype=p.xtype
WHERE 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?

Answer 1

It seems to me that you need a profiling tool. You can use Visual Studio 2008 Database Edition, or an Open Source alternative such as DataCleaner.

Answer 2

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_ONLY
FOR SELECT t.name AS [Table], c.name AS [COLUMN]
FROM dbo.sysobjects AS t
JOIN dbo.syscolumns AS c ON t.id=c.id
JOIN dbo.systypes AS p ON c.xtype=p.xtype
WHERE t.xtype='U'
  and p.name = 'int'
  and p.prec is not null

OPEN fields

FETCH NEXT FROM fields 
INTO @Table, @Column

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'SELECT ''' + @Table + '.' + @Column + ''', Avg(' + @Column+ ') FROM ' + @Table
    EXEC(@Sql)

    FETCH NEXT FROM fields 
    INTO @Table, @Column

END
CLOSE fields
DEALLOCATE fields

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