May 23, 2002 at 11:44 am
I saw the script on this website that counts the number of rows in a table. I modified it to return only "User" databases:
use newdb
select o.name as "Table Name",i.rowcnt as "Row Count"
from sysobjects o, sysindexes i
where i.id = o.id
and indid in(0,1)
and o.type = 'U'
order by o.name
In certain databases this script returns an additional table, "dtproperties". Under EM (Sql 2K) that table is shown as "System" but in sysobjects it has type "U". How do I modify the script to only return User tables?
TIA,
Bill
P.S. BTW what does "indid" refer to in the script?
May 24, 2002 at 1:03 am
Hi,
I recently asked what 'indid = 0' meant and was shown a (old but still true) document. Please refer to that thread on this forum.
Basically it's 'type of index'.
With Kind Regards/Met vriendelijke groet
Schil
'It's never ending and never surrendering' Unida 1999
May 24, 2002 at 8:45 am
dtproperties is a problem child because of how EM treats it. Typically, I select it out.
use newdb
select o.name as "Table Name",i.rowcnt as "Row Count"
from sysobjects o, sysindexes i
where i.id = o.id
and indid in(0,1)
and o.type = 'U'
and o.name <> 'dtproperties'
order by o.name
indid = 0 is the sysindex row for a table with no clustered index. indid = 1 is the sysindex row for a table with a clustered index. Here's a basic script to try in Query Analyzer so you can see how indid works.
/* No indexes. Heap Table.
Should only have indid = 0 */
CREATE TABLE NoIndexes
(NoIndexesID int)
GO
/* Clustered Index.
Should only have indid = 1 */
CREATE TABLE ClusteredOnly
(ClusteredOnlyID int)
GO
CREATE CLUSTERED INDEX IDX_ClusteredOnly
ON ClusteredOnly (ClusteredOnlyID)
GO
/* Heap table with nonclustered index.
We should see indid = 0 and indid = 2. */
CREATE TABLE NonClusteredOnly
(NonClusteredOnlyID int)
GO
CREATE INDEX NDX_NonClusteredOnly
ON NonClusteredOnly (NonClusteredOnlyID)
GO
/* Clustered index table with nonclustered index.
We should see indid = 1 and indid = 2. */
CREATE TABLE BothIndexes (
ClusteredID int,
NonClusteredID int)
GO
CREATE CLUSTERED INDEX IDX_BothIndexes
ON BothIndexes (ClusteredID)
GO
CREATE INDEX NDX_BothIndexes
ON BothIndexes (NonClusteredID)
GO
/* Show our indexes */
SELECT DISTINCT object_name(si.id) [Table], si.id, si.indid
FROM sysindexes si JOIN sysobjects so
ON si.id = so.id
WHERE so.type = 'U'
ORDER BY si.id, si.indid
GO
/* Clean up */
DROP TABLE NoIndexes
GO
DROP TABLE ClusteredOnly
GO
DROP TABLE NonClusteredOnly
GO
DROP TABLE BothIndexes
GO
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
May 24, 2002 at 9:31 am
Thanks for the help guys. One other glitch. When I run the script on a SQL 2K machine the "row count" column displays as a decimal but under our Sql 7 machines it displays as hex! (e.g. 0x7200000000000000). Any thoughts on why this is happening or what to do about it?
May 24, 2002 at 12:41 pm
Here's another detail:
under SQL 7: sysindexes\rowcount field is a <binary> field
under SQL 2K: sysindexes\rowcount field is a bigint!
May 24, 2002 at 1:20 pm
After further examination I think the t-sql is using the wrong column. Instead of "rowcnt" is should use "rows" which is present in both sql7/sql2k sysindexes.
use newbie
select o.name as "Table Name",i.rows as "Row Count"
from sysobjects o, sysindexes i
where i.id = o.id
and indid in(0,1)
and o.type = 'U'
and o.name <> 'dtproperties'
order by o.name
May 30, 2002 at 4:48 am
I've been using the status field from sysobjects (SQL Server 2000) to select out system tables. This seems to work for all databases I've tried except Northwind! I know it's not a documented field so it shouldn't be used, but it's been quite useful for a couple of utility apps.
select a1.name, a1.xtype
from
sysobjects a1 left outer join syscomments a2 on
a1.id = a2.id
left outer join sysobjects a3 on
a1.parent_obj = a3.id
where
a1.status >= 0 and isnull(a3.status,0) >= 0
Has anyone got a good way of filtering out those "_WA_sys_" system indexes from sysindexes??
Cheers
Bernard
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply