SQL Server 2000:我如何获得表和行计数的列表? [英] SQL Server 2000: how do i get a list of tables and the row counts?
本文介绍了SQL Server 2000:我如何获得表和行计数的列表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我知道我可以通过
获取一个表的列表。SELECT TABLE_NAME FROM information_schema.tables
WHERE NOT TABLE_NAME ='sysdiagrams'
AND TABLE_SCHEMA ='dbo'
AND TABLE_TYPE ='BASE TABLE'
但我不知道如何修改,以获得第二列与当前行的表计数。我虽然是这样的:
DECLARE @tbl VARCHAR(200)
(SELECT @tbl = TABLE_NAME,TABLE_NAME ,
(SELECT COUNT(ID)AS Cnt FROM @tbl)
FROM information_schema.tables
WHERE NOT TABLE_NAME ='sysdiagrams'
AND TABLE_SCHEMA ='dbo'
AND TABLE_TYPE ='BASE TABLE')
我知道上面的是无效的T-SQL,它得到了我会像所做的那样的点。这是为SQL Server 2000.我不喜欢使用存储过程如果可能的话。
解决方案(包括未提交的更改和堆上的可能的转发指针)
select o.name,rows
从sysindexes i连接sysobjects o on o.id = i.id
其中indid< 2和type ='U'
I know that I can get a list of tables with
SELECT TABLE_NAME FROM information_schema.tables WHERE NOT TABLE_NAME='sysdiagrams' AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE= 'BASE TABLE'
But I'm not sure how to modify that to get a 2nd column with the current count of rows for the tables. I though of something like this:
DECLARE @tbl VARCHAR(200) (SELECT @tbl = TABLE_NAME, TABLE_NAME, (SELECT COUNT(ID) AS Cnt FROM @tbl) FROM information_schema.tables WHERE NOT TABLE_NAME='sysdiagrams' AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE= 'BASE TABLE')
I know the above is not valid T-SQL but I think it gets the point of what I would like the have done. This is for SQL Server 2000. I would prefer not to use store procedures if at all possible.
解决方案A quick and dirty way (includes uncommitted changes and possibly forwarding pointers on heaps)
select o.name, rows from sysindexes i join sysobjects o on o.id=i.id where indid < 2 and type='U'
这篇关于SQL Server 2000:我如何获得表和行计数的列表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文