数据库数据行数等统计

SELECT  a.name ,
        b.rows
FROM    sysobjects AS a
        INNER JOIN sysindexes AS b ON a.id = b.id
WHERE   ( a.type = ‘u‘ )
        AND ( b.indid IN ( 0, 1 ) )
        AND a.name IN ( ‘IS_FORM_GDJD‘, ‘T_WaterFormula‘, ‘GD_FORM_YCSBJFBZ‘,
                        ‘WS_ISSUE‘, ‘IS_FORM_QSMASTER‘, ‘IS_FORM_SBXJ‘,
                        ‘SYS_WEATHER‘, ‘IS_FORM_ZXPH‘, ‘IS_FORM_BFXJ‘,
                        ‘IS_FORM_YSKWTSB‘, ‘IS_FORM_XFBTSJC‘,
                        ‘FW_FACILITY_SBXJRY‘, ‘IS_FORM_XFSWTSB‘,
                        ‘WS_AGENT_RULE‘, ‘GD_FORM_SZTS‘, ‘IS_FORM_YJPSH‘,
                        ‘FW_COUNT_PATROL_ISSUE‘, ‘IS_FORM_LDSHSB‘,
                        ‘WS_APP_REGISTER‘, ‘FW_COUNT_PATROL_OBJECT‘,
                        ‘FW_COUNT_PATROL_PERSON‘, ‘GD_FORM_HLDWQR‘,
                        ‘WS_APP_SETTING‘, ‘IS_FORM_FMWTSB‘,
                        ‘FW_COUNT_PATROL_SUMMARY‘, ‘WS_USERSETTING‘,
                        ‘GD_FORM_FMWXX‘, ‘FW_FACILITY_QYS‘, ‘WS_ATTACHMENT‘,
                        ‘FW_GRID‘, ‘WS_ONDUTY‘, ‘GD_MX‘, ‘WS_BIZDB_CONN‘,
                        ‘FW_PATROL_CKPOINT‘, ‘WS_ONDUTY_PLAN‘, ‘WS_INST_STEP‘,
                        ‘IS_FORM_QSCHECK‘, ‘Misc‘, ‘WS_BIZDB_DB_TYPE‘,
                        ‘FW_PATROL_GIS_RELATION‘, ‘FW_PATROL_ISSUE_RELATION‘,
                        ‘FW_PATROL_OBJECT‘, ‘GD_FORM_QSCHECK‘,
                        ‘FW_PATROL_OBJECT_REPORT‘, ‘IS_FORM_RICHANG‘,
                        ‘IS_FORM_GDXC‘, ‘FW_PATROL_PERIOD‘, ‘FW_FACILITY_CYD‘,
                        ‘IS_FORM_QYS‘, ‘WS_BIZFIELD_MAPPER‘, ‘IS_FORM_JCD‘,
                        ‘FW_PATROL_PLAN‘, ‘WS_CONTROL_TYPE‘, ‘IS_FORM_QSWORK‘,
                        ‘FW_PATROL_PLAN_CHANGELOG‘, ‘FW_CONSTRUCTION_SITE‘,
                        ‘WS_SNAPSHOT‘, ‘IS_FORM_QS_DELETE‘,
                        ‘FW_FLOOD_CONTROL_DUTYBRIEF‘, ‘X_TROUBLE‘,
                        ‘WS_ENTITY_FIELD‘, ‘IS_FORM_QTWTSB‘, ‘FW_PATROL_TASK‘,
                        ‘WS_GIS_DISPATCH_WEIGHT‘, ‘FW_PATROL_TASK_CKPOINT‘,
                        ‘LHSBMX‘, ‘FW_USER‘, ‘GD_FORM_BWHFYS‘, ‘UV_SBWXXXID‘,
                        ‘statics_bw‘, ‘WS_EXTREME_WEATHER‘, ‘IS_FORM_SBGZ‘,
                        ‘IS_FORM_JWSS‘, ‘FW_VEHICLE‘, ‘WS_INST_ATTENTION‘,
                        ‘WS_INST_PROCESS_RECORD‘, ‘IS_FORM_JGWTSB‘,
                        ‘statics_jjd‘, ‘WS_INST_COMMUNICATION‘, ‘IS_FORM_SCQX‘,
                        ‘IS_FORM_XQSCSB‘, ‘WS_INST_DELAY‘, ‘WS_OUTSOURCINGSET‘,
                        ‘GD_FORM_SZJC‘, ‘WS_INST_FIELDSTAFF‘, ‘UV_BWSBKJXH‘,
                        ‘WS_INST_KEYPOINT‘, ‘IS_FORM_XQGWSB‘, ‘IS_FORM_SZCY‘,
                        ‘LHMX‘, ‘WS_INST_LOG‘, ‘IS_FORM_GDWTSB‘,
                        ‘GD_FORM_XQSCZC‘, ‘WS_INST_OPERATOR‘,
                        ‘WS_INST_OPERATOR_UPLOAD‘, ‘GD_FORM_XQGWZC‘,
                        ‘WS_INST_PLAN‘, ‘WS_INST_SHARE_DELETE‘,
                        ‘GD_FORM_TSXQSC‘, ‘IS_FORM_FHPLZSRW‘,
                        ‘WS_INST_STEP_AGENT‘, ‘GD_FORM_TSXQGW‘,
                        ‘IS_FORM_FHPLSYSJ‘, ‘WS_INST_STEP_UPLOADATTACHMENT‘,
                        ‘WS_INST_SUSPENSION‘, ‘INDICATORS‘,
                        ‘WS_INST_STEP_STATSTIC‘, ‘UV_SZJCLXZL‘, ‘WS_INST_URGE‘,
                        ‘UV_BWMODEL‘, ‘WS_INST_SNAPSHOT‘, ‘WS_IS2WS_RELATION‘,
                        ‘WS_IS2WS_MAPPER‘, ‘UV_BWMANU‘, ‘WS_KEYPOINT‘,
                        ‘GD_FORM_SYTS‘, ‘WS_NOTIFY_SUBSCRIBER‘,
                        ‘WS_PROCESS_SCHEMA‘, ‘WS_SEQ‘, ‘GD_FORM_QYS‘,
                        ‘WS_SHORTTEXT‘, ‘GD_FORM_BWDGZQHB‘, ‘GD_FORM_BWGZHB‘,
                        ‘GD_FORM_QS_DELETE‘, ‘WS_TYPE‘, ‘GD_FORM_BJ‘,
                        ‘IS_FORM_SS‘, ‘WS_TYPE_AUTH‘, ‘UV_BZYBZL‘,
                        ‘UV_BWGZXXID‘, ‘WS_TYPE_DFCFG‘, ‘GD_FORM_TSSBWX‘,
                        ‘GD_FORM_TSHFYS‘, ‘GD_FORM_BWSBWX‘, ‘GD_FORM_SS‘,
                        ‘GD_FORM_BWCB‘, ‘FW_FACILITY_JCD‘, ‘GD_FORM_BWWZCB‘,
                        ‘GD_FORM_FMWXS‘, ‘GD_FORM_BWSBJCCZB‘, ‘CNF_WORD‘,
                        ‘GD_FORM_BWFPLHB‘, ‘UV_FMBYWTFK‘, ‘GD_FORM_BWZQXPLHB‘,
                        ‘GDIData‘, ‘GD_FORM_JCDYW‘, ‘WS_BIZDB_FORM‘,
                        ‘IS_FORM_YJPSHJHG‘, ‘UV_BZHZSM‘, ‘IS_FORM_PSKPL‘,
                        ‘UV_BZSQLX‘, ‘FW_DUTYGUARD_WORKSHEET_RELATION‘,
                        ‘GD_FORM_XFBTS‘, ‘UV_BZJSGS‘, ‘IS_FORM_PSK‘,
                        ‘GD_FORM_JL‘, ‘WS_BIZDB_TABLE‘, ‘GD_FORM_PSGDWX‘,
                        ‘IS_FORM_FMBY‘, ‘GD_FORM_QSMASTER‘,
                        ‘FW_FLOOD_CONTROL_DUTYGUARD‘, ‘UV_SBBFXJMC‘,
                        ‘GD_FORM_SSWX‘, ‘IS_FORM_XHSBY‘, ‘WS_VOICE_PHONE‘,
                        ‘GD_FORM_TSXC‘, ‘WS_INST_DISPATCH_LOG‘, ‘GD_FORM_YHTS‘,
                        ‘WS_SEND_VOICE‘, ‘GD_FORM_XHSWX‘, ‘GD_FORM_JCD‘,
                        ‘IS_FORM_FHPL‘, ‘IS_FORM_JCJWTSB‘, ‘IS_FORM_BFYH‘,
                        ‘WS_INST‘, ‘GD_FORM_FHPLZSRW‘, ‘GD_FORM_FHPLJSRW‘,
                        ‘GD_FORM_GDWX‘, ‘GD_FORM_QSWORK‘,
                        ‘FW_FLOOD_CONTROL_DUTYPOINT‘, ‘GD_FORM_SBGZ‘ )
ORDER BY a.name ,
        b.rows DESC;

EXEC sp_spaceused @updateusage = N‘TRUE‘;

 IF EXISTS ( SELECT 1
             FROM   tempdb..sysobjects
             WHERE  id = OBJECT_ID(‘tempdb..#tabName‘)
                    AND xtype = ‘u‘ )
    DROP TABLE #tabName;
GO
 CREATE TABLE #tabName
    (
      tabname VARCHAR(100) ,
      rowsNum VARCHAR(100) ,
      reserved VARCHAR(100) ,
      data VARCHAR(100) ,
      index_size VARCHAR(100) ,
      unused_size VARCHAR(100)
    );
 
 DECLARE @name VARCHAR(100);
 DECLARE cur CURSOR
 FOR
    SELECT  name
    FROM    sysobjects
    WHERE   xtype = ‘u‘
    ORDER BY name;
 OPEN cur;
 FETCH NEXT FROM cur INTO @name;
 WHILE @@fetch_status = 0
    BEGIN
        INSERT  INTO #tabName
                EXEC sp_spaceused @name;
    --print @name
 
        FETCH NEXT FROM cur INTO @name;
    END;
 CLOSE cur;
 DEALLOCATE cur;
------------ 已经经过优化
 SELECT tabname AS ‘表名‘ ,
        rowsNum AS ‘表数据行数‘ ,
        reserved AS ‘保留大小‘ ,
        CONVERT(INT, SUBSTRING(data, 0, LEN(data) - 2)) size ,
        data AS ‘数据大小‘ ,
        index_size AS ‘索引大小‘ ,
        unused_size AS ‘未使用大小‘
 FROM   #tabName
 WHERE  tabname IN ( ‘IS_FORM_GDJD‘, ‘T_WaterFormula‘, ‘GD_FORM_YCSBJFBZ‘,
                     ‘WS_ISSUE‘, ‘IS_FORM_QSMASTER‘, ‘IS_FORM_SBXJ‘,
                     ‘SYS_WEATHER‘, ‘IS_FORM_ZXPH‘, ‘IS_FORM_BFXJ‘,
                     ‘IS_FORM_YSKWTSB‘, ‘IS_FORM_XFBTSJC‘,
                     ‘FW_FACILITY_SBXJRY‘, ‘IS_FORM_XFSWTSB‘, ‘WS_AGENT_RULE‘,
                     ‘GD_FORM_SZTS‘, ‘IS_FORM_YJPSH‘, ‘FW_COUNT_PATROL_ISSUE‘,
                     ‘IS_FORM_LDSHSB‘, ‘WS_APP_REGISTER‘,
                     ‘FW_COUNT_PATROL_OBJECT‘, ‘FW_COUNT_PATROL_PERSON‘,
                     ‘GD_FORM_HLDWQR‘, ‘WS_APP_SETTING‘, ‘IS_FORM_FMWTSB‘,
                     ‘FW_COUNT_PATROL_SUMMARY‘, ‘WS_USERSETTING‘,
                     ‘GD_FORM_FMWXX‘, ‘FW_FACILITY_QYS‘, ‘WS_ATTACHMENT‘,
                     ‘FW_GRID‘, ‘WS_ONDUTY‘, ‘GD_MX‘, ‘WS_BIZDB_CONN‘,
                     ‘FW_PATROL_CKPOINT‘, ‘WS_ONDUTY_PLAN‘, ‘WS_INST_STEP‘,
                     ‘IS_FORM_QSCHECK‘, ‘Misc‘, ‘WS_BIZDB_DB_TYPE‘,
                     ‘FW_PATROL_GIS_RELATION‘, ‘FW_PATROL_ISSUE_RELATION‘,
                     ‘FW_PATROL_OBJECT‘, ‘GD_FORM_QSCHECK‘,
                     ‘FW_PATROL_OBJECT_REPORT‘, ‘IS_FORM_RICHANG‘,
                     ‘IS_FORM_GDXC‘, ‘FW_PATROL_PERIOD‘, ‘FW_FACILITY_CYD‘,
                     ‘IS_FORM_QYS‘, ‘WS_BIZFIELD_MAPPER‘, ‘IS_FORM_JCD‘,
                     ‘FW_PATROL_PLAN‘, ‘WS_CONTROL_TYPE‘, ‘IS_FORM_QSWORK‘,
                     ‘FW_PATROL_PLAN_CHANGELOG‘, ‘FW_CONSTRUCTION_SITE‘,
                     ‘WS_SNAPSHOT‘, ‘IS_FORM_QS_DELETE‘,
                     ‘FW_FLOOD_CONTROL_DUTYBRIEF‘, ‘X_TROUBLE‘,
                     ‘WS_ENTITY_FIELD‘, ‘IS_FORM_QTWTSB‘, ‘FW_PATROL_TASK‘,
                     ‘WS_GIS_DISPATCH_WEIGHT‘, ‘FW_PATROL_TASK_CKPOINT‘,
                     ‘LHSBMX‘, ‘FW_USER‘, ‘GD_FORM_BWHFYS‘, ‘UV_SBWXXXID‘,
                     ‘statics_bw‘, ‘WS_EXTREME_WEATHER‘, ‘IS_FORM_SBGZ‘,
                     ‘IS_FORM_JWSS‘, ‘FW_VEHICLE‘, ‘WS_INST_ATTENTION‘,
                     ‘WS_INST_PROCESS_RECORD‘, ‘IS_FORM_JGWTSB‘, ‘statics_jjd‘,
                     ‘WS_INST_COMMUNICATION‘, ‘IS_FORM_SCQX‘, ‘IS_FORM_XQSCSB‘,
                     ‘WS_INST_DELAY‘, ‘WS_OUTSOURCINGSET‘, ‘GD_FORM_SZJC‘,
                     ‘WS_INST_FIELDSTAFF‘, ‘UV_BWSBKJXH‘, ‘WS_INST_KEYPOINT‘,
                     ‘IS_FORM_XQGWSB‘, ‘IS_FORM_SZCY‘, ‘LHMX‘, ‘WS_INST_LOG‘,
                     ‘IS_FORM_GDWTSB‘, ‘GD_FORM_XQSCZC‘, ‘WS_INST_OPERATOR‘,
                     ‘WS_INST_OPERATOR_UPLOAD‘, ‘GD_FORM_XQGWZC‘,
                     ‘WS_INST_PLAN‘, ‘WS_INST_SHARE_DELETE‘, ‘GD_FORM_TSXQSC‘,
                     ‘IS_FORM_FHPLZSRW‘, ‘WS_INST_STEP_AGENT‘,
                     ‘GD_FORM_TSXQGW‘, ‘IS_FORM_FHPLSYSJ‘,
                     ‘WS_INST_STEP_UPLOADATTACHMENT‘, ‘WS_INST_SUSPENSION‘,
                     ‘INDICATORS‘, ‘WS_INST_STEP_STATSTIC‘, ‘UV_SZJCLXZL‘,
                     ‘WS_INST_URGE‘, ‘UV_BWMODEL‘, ‘WS_INST_SNAPSHOT‘,
                     ‘WS_IS2WS_RELATION‘, ‘WS_IS2WS_MAPPER‘, ‘UV_BWMANU‘,
                     ‘WS_KEYPOINT‘, ‘GD_FORM_SYTS‘, ‘WS_NOTIFY_SUBSCRIBER‘,
                     ‘WS_PROCESS_SCHEMA‘, ‘WS_SEQ‘, ‘GD_FORM_QYS‘,
                     ‘WS_SHORTTEXT‘, ‘GD_FORM_BWDGZQHB‘, ‘GD_FORM_BWGZHB‘,
                     ‘GD_FORM_QS_DELETE‘, ‘WS_TYPE‘, ‘GD_FORM_BJ‘,
                     ‘IS_FORM_SS‘, ‘WS_TYPE_AUTH‘, ‘UV_BZYBZL‘, ‘UV_BWGZXXID‘,
                     ‘WS_TYPE_DFCFG‘, ‘GD_FORM_TSSBWX‘, ‘GD_FORM_TSHFYS‘,
                     ‘GD_FORM_BWSBWX‘, ‘GD_FORM_SS‘, ‘GD_FORM_BWCB‘,
                     ‘FW_FACILITY_JCD‘, ‘GD_FORM_BWWZCB‘, ‘GD_FORM_FMWXS‘,
                     ‘GD_FORM_BWSBJCCZB‘, ‘CNF_WORD‘, ‘GD_FORM_BWFPLHB‘,
                     ‘UV_FMBYWTFK‘, ‘GD_FORM_BWZQXPLHB‘, ‘GDIData‘,
                     ‘GD_FORM_JCDYW‘, ‘WS_BIZDB_FORM‘, ‘IS_FORM_YJPSHJHG‘,
                     ‘UV_BZHZSM‘, ‘IS_FORM_PSKPL‘, ‘UV_BZSQLX‘,
                     ‘FW_DUTYGUARD_WORKSHEET_RELATION‘, ‘GD_FORM_XFBTS‘,
                     ‘UV_BZJSGS‘, ‘IS_FORM_PSK‘, ‘GD_FORM_JL‘,
                     ‘WS_BIZDB_TABLE‘, ‘GD_FORM_PSGDWX‘, ‘IS_FORM_FMBY‘,
                     ‘GD_FORM_QSMASTER‘, ‘FW_FLOOD_CONTROL_DUTYGUARD‘,
                     ‘UV_SBBFXJMC‘, ‘GD_FORM_SSWX‘, ‘IS_FORM_XHSBY‘,
                     ‘WS_VOICE_PHONE‘, ‘GD_FORM_TSXC‘, ‘WS_INST_DISPATCH_LOG‘,
                     ‘GD_FORM_YHTS‘, ‘WS_SEND_VOICE‘, ‘GD_FORM_XHSWX‘,
                     ‘GD_FORM_JCD‘, ‘IS_FORM_FHPL‘, ‘IS_FORM_JCJWTSB‘,
                     ‘IS_FORM_BFYH‘, ‘WS_INST‘, ‘GD_FORM_FHPLZSRW‘,
                     ‘GD_FORM_FHPLJSRW‘, ‘GD_FORM_GDWX‘, ‘GD_FORM_QSWORK‘,
                     ‘FW_FLOOD_CONTROL_DUTYPOINT‘, ‘GD_FORM_SBGZ‘ )
 ORDER BY size DESC;  

select dt=convert(varchar(13),CREATE_TIME,120),
       qty=count(1)
 from dbo.WS_INST_STEP
 group by convert(varchar(13),CREATE_TIME,120)
 order by convert(varchar(13),CREATE_TIME,120)