34,590
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('tempdb..#stats') IS NOT NULL
BEGIN
DROP TABLE #stats
END
SELECT id INTO #stats FROM dbo.sysobjects WHERE xtype = 'U' AND [name] <> 'dtproperties'
ALTER TABLE #stats ADD [max] NVARCHAR(50)
DECLARE
@id INT,
@t_name nvarchar(50),
@c_name nvarchar(50),
@c_max varchar(50)
DECLARE c_cursor CURSOR FOR
SELECT a.id, 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
@id, @t_name, @c_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('UPDATE #stats SET [max] = (SELECT MAX(' + @c_name + ') FROM ' + @t_name + ') WHERE id=' + @id)
FETCH NEXT FROM c_cursor INTO @id, @t_name, @c_name
END
CLOSE c_cursor
DEALLOCATE c_cursor
SELECT * FROM #stats ORDER BY id
IF OBJECT_ID('tempdb..#stats') IS NOT NULL
BEGIN
DROP TABLE #stats
END
SELECT tab_name AS t_name, [col_name] AS c_name, [type] INTO #stats FROM dbo.TableView
ALTER TABLE #stats ADD c_max NVARCHAR(50), c_min NVARCHAR(50), c_countrows INT, c_countvalid INT
GO
DECLARE
@t_name nvarchar(50),
@c_name nvarchar(50),
@c_type nvarchar(50),
@c_max nvarchar(50),
@c_min nvarchar(50),
@c_countrows int,
@c_countvalid int
DECLARE c_cursor CURSOR FOR
SELECT * FROM #stats
OPEN c_cursor
FETCH NEXT FROM c_cursor INTO
@t_name, @c_name, @c_type, @c_max, @c_min, @c_countrows, @c_countvalid
WHILE @@FETCH_STATUS = 0
BEGIN
IF @c_type='datetime' OR @c_type='int' OR @c_type='decimal' OR @c_type='float' OR @c_type='real'
BEGIN
PRINT ('UPDATE #stats SET c_max=(SELECT MAX(' + @c_name + ') FROM ' + @t_name + '),c_min=(SELECT MIN(' + @c_name + ') FROM ' + @t_name + '),c_countrows=(SELECT COUNT(*) FROM' + @t_name + '),c_countvalid=(SELECT COUNT(' + @c_name + ') FROM ' + @t_name + ') WHERE t_name=''' + @t_name + ''' AND c_name=''' + @c_name + '''')
END
EXEC ('UPDATE #stats SET c_countrows=(SELECT COUNT(*) FROM ' + @t_name + '),c_countvalid=(SELECT COUNT(' + @c_name + ') FROM ' + @t_name + ') WHERE t_name=''' + @t_name + ''' AND c_name=''' + @c_name + '''')
FETCH NEXT FROM c_cursor INTO
@t_name, @c_name, @c_type, @c_max, @c_min, @c_countrows, @c_countvalid
END
CLOSE c_cursor
DEALLOCATE c_cursor
GO
ALTER TABLE #stats ADD c_completeness FLOAT
GO
UPDATE #stats SET c_completeness=CONVERT(FLOAT,ISNULL(c_countvalid,0))/(CASE WHEN CONVERT(FLOAT,ISNULL(c_countrows,0))=0 THEN -1 ELSE CONVERT(FLOAT,ISNULL(c_countrows,0)) END)
GO
SELECT * FROM #stats ORDER BY t_name, c_name
DROP TABLE #stats
GO