我想统计数据库中所有表的字段特征。要有下面的属性:table name column name Data type Length Count null Count distinct Count not null Completeness Min Max
-----------+--------------+-----------+------+----------+--------------+--------------+------------+---+---
table1 id int 4 0 5429 5429 100% 1 5429
table1 nu int 4 231 4301 5198 95.7% 1 22321
-----------+--------------+-----------+------+----------+--------------+--------------+------------+---+---大多属性可以通过sys表得到(比如类似下面的view可以得到近似的表)。但如何统计null多少,最大,最小值呢?有些字段可能是text或者bit之类应显示空或者数字而不能报错。SELECT TOP 100 PERCENT so.name AS [Table], sc.name AS [Column], st.name AS Expr1, st.length AS Expr2, st.status AS Expr3, sc.name, sc.id, sc.xscale
FROM dbo.sysobjects AS so INNER JOIN
dbo.syscolumns AS sc ON sc.id = so.id INNER JOIN
dbo.systypes AS st ON st.type = sc.type
WHERE (so.type = 'U')
ORDER BY [Table], [Column]
---------------
id 450
name zzz
...
---------------最好是一句sql,因为最终要做到视图中去。
select 字段,max(字段) from 表 group by 字段
SELECT so.name AS [Table], sc.name AS [Column], max(sc.name) as [Max]
FROM dbo.sysobjects AS so INNER JOIN
dbo.syscolumns AS sc ON sc.id = so.id
WHERE (so.type = 'U')
group by sc.name
ORDER BY [Table], [Column]
Msg 8120, Level 16, State 1, Line 1
Column 'so.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
@t_name nvarchar(50),
@c_name nvarchar(50),
@c_max varchar(50)
DECLARE c_cursor cursor FOR
SELECT a.name, b.name FROM dbo.sysobjects a inner join dbo.syscolumns b on a.id=b.id WHERE a.xtype = 'U' AND a.name <> 'dtproperties'
OPEN c_cursor
fetch next from c_cursor into
@t_name, @c_name
WHILE @@FETCH_STATUS = 0
BEGIN
select max(@c_name) from @t_name
fetch next from c_cursor into @t_name, @c_name
END
CLOSE c_cursor
DEALLOCATE c_cursor