select TableName, Desc ,count(TableName) as 记录个数 from A group by TableName
internetcsdn理解是对的,但语句错误 对象名 'aa.tablename' 无效。
master里的表sysobjects好像有记录这些数据的. 至少具体是什么,我就不清楚
Select a.name as 表名,isnull(b.rows,0) as 记录个数 from sysobjects a left join sysindexes b on a.name=b.name where a.xtype = 'U' and a.status>0
OutWindows产生结果不对因为重写程序,不能改动数据库,所以不能使用子定义函数
OutWindows 语法正确,但数据不正确,
结果是列出数据库里用户表及相应表里的数据记录数 我这里的结果在本机正确,在一台服务器上有问题,现修改如下:Select a.name as 表名,isnull(max(b.rows),0) as 记录个数 from sysobjects a left join sysindexes b on a.id=b.id where a.xtype = 'U' and a.status>0 group by a.name
--经过再次测试,①句结果不正确,②句结果正确... --表的记录数 --① Select a.name as 表名,isnull(b.rows,0) as 记录个数 from sysobjects a left join sysindexes b on a.name=b.name where a.xtype = 'U' and a.status>0 order by a.name --② Select a.name as 表名,isnull(max(b.rows),0) as 记录个数 from sysobjects a left join sysindexes b on a.id=b.id where a.xtype = 'U' and a.status>0 group by a.name order by a.name
这个问题很简单:select *,记录个数=I.ROWS from a aa,SYSINDEXES I WHERE I.ID=OBJECT_ID(AA.TABLENAME) AND I.INDID<2
--② Select a.name as 表名,isnull(max(b.rows),0) as 记录个数 from sysobjects a left join sysindexes b on a.id=b.id where a.xtype = 'U' and a.status>0 group by a.name order by a.name也不正确,如果一个表有对多个索引,而且最近删除过数据
对象名 'aa.tablename' 无效。
至少具体是什么,我就不清楚
from sysobjects a left join sysindexes b on a.name=b.name
where a.xtype = 'U' and a.status>0
我这里的结果在本机正确,在一台服务器上有问题,现修改如下:Select a.name as 表名,isnull(max(b.rows),0) as 记录个数
from sysobjects a left join sysindexes b on a.id=b.id
where a.xtype = 'U' and a.status>0
group by a.name
--表的记录数
--①
Select a.name as 表名,isnull(b.rows,0) as 记录个数
from sysobjects a left join sysindexes b on a.name=b.name
where a.xtype = 'U' and a.status>0
order by a.name
--②
Select a.name as 表名,isnull(max(b.rows),0) as 记录个数
from sysobjects a left join sysindexes b on a.id=b.id
where a.xtype = 'U' and a.status>0
group by a.name
order by a.name
WHERE I.ID=OBJECT_ID(AA.TABLENAME)
AND I.INDID<2
Select a.name as 表名,isnull(max(b.rows),0) as 记录个数
from sysobjects a left join sysindexes b on a.id=b.id
where a.xtype = 'U' and a.status>0
group by a.name
order by a.name也不正确,如果一个表有对多个索引,而且最近删除过数据