T-SQL query to sum numeric fields in SQL Server 2000 db
I have a SQL Server 2000 db and I would like to get summary information for all numeric fields contained in custom database tables.
I can get names, data types 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 have the average contained in each field?
Can this be done with a subquery, or do I need to put the result of this query in a cursor and loop through a second select query for each column?
0
a source to share
2 answers
My best and quickest guess is to use 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
The temporary table should work fine as well. You can also add Min () and Max () values. Hope it helps.
+1
a source to share
It seems to me that you need a profiling tool. You can use Visual Studio 2008 Database Edition or an open source alternative like DataCleaner .
0
a source to share