November 7, 2014 at 5:23 am
Actually, I have an application which is having some problem. To see where is the problem I am trying to count rows in the database.
First I create a table A with 2 columns namely tablename, rowbefore and rowafter and I insert records in it as below.
INSERT INTO A
SELECT TableName = o.name, '', Rows = max(i.rows) FROM sysobjects o
INNER JOIN sysindexes i ON o.id = i.id
WHERE xtype = 'u' AND OBJECTPROPERTY(o.id,N'IsUserTable') = 1
GROUP BY o.name
ORDER BY o.name
Then I update rowbefore with rowafter as below.
UPDATE A SET rowbefore = rowafter
Now I launch my application with update records in the database.
Then I am trying to update rowafter with new records as below.
UPDATE A
SET rowafter = (SELECT max(sysindexes.rows) FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE xtype = 'u' AND OBJECTPROPERTY(sysobjects.id,N'IsUserTable') = 1 AND A.tablename = sysobjects.name)
Does this update really update my column rowafter or not? Please help!!
November 7, 2014 at 5:46 am
Simple test !
UPDATE A SET rowbefore = rowafter, rowafter = -1 ;
Select * from a order by TableName ;
UPDATE A
SET rowafter = (SELECT max(sysindexes.rows) FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE xtype = 'u' AND OBJECTPROPERTY(sysobjects.id,N'IsUserTable') = 1 AND A.tablename = sysobjects.name)
Select * from a order by TableName ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 8, 2014 at 3:06 am
Quick thought, it might be easier to query the sys.partitions for the row count
😎
;WITH LIST_TABLE AS
(
SELECT
T.object_id
,T.is_ms_shipped
FROM sys.tables T
)
SELECT
OBJECT_NAME( LT.object_id ) AS TABLE_SCHEMA
,OBJECT_SCHEMA_NAME( LT.object_id ) AS TABLE_NAME
,SUM(SP.rows) AS TABLE_ROWS
FROM LIST_TABLE LT
INNER JOIN sys.partitions SP
ON LT.object_id = SP.object_id
WHERE SP.index_id IN (1,0)
AND LT.is_ms_shipped = 0
GROUP BY LT.object_id;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply