October 23, 2014 at 9:25 pm
I cannot figure out how to count the rows of a table that I pull from sys.tables..
So here is the starting code.
Select top 1 name for sys.tables
the table that gets pulled is ContactUpdates.. Now question is how do i get the count of the row from this table.. I cannot write a query that uses the table name directly because I am not suppose to know what table is the first table in sys.tables...i tried storing it to a variable and then doing a count on the variable but that didnt work
October 23, 2014 at 10:19 pm
Quick solution, should get you passed this hurdle
😎
;WITH LAST_TABLE AS
(
SELECT TOP(1)
T.object_id
,T.is_ms_shipped
FROM sys.tables T
ORDER BY T.object_id DESC
)
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 LAST_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;
October 24, 2014 at 7:08 am
Eirikur Eiriksson (10/23/2014)
Quick solution, should get you passed this hurdle😎
; --terminator, not begininator :-PWITH LAST_TABLE AS
(
SELECT TOP(1)
T.object_id
,T.is_ms_shipped
FROM sys.tables T
ORDER BY T.object_id DESC
)
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 LAST_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;
Approximate count of rows in a partition, right? I'd have thought you'd have to run "DBCC UPDATEUSAGE" with the COUNT_ROWS option first to give you any semblance of a guarantee of a correct count. The other option being a dynamic SQL method building a query based on sys.tables then executing that.
October 24, 2014 at 9:07 am
Cadavre (10/24/2014)
Eirikur Eiriksson (10/23/2014)
Quick solution, should get you passed this hurdle😎
; --terminator, not begininator :-PWITH LAST_TABLE AS
(
SELECT TOP(1)
T.object_id
,T.is_ms_shipped
FROM sys.tables T
ORDER BY T.object_id DESC
)
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 LAST_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;
Approximate count of rows in a partition, right? I'd have thought you'd have to run "DBCC UPDATEUSAGE" with the COUNT_ROWS option first to give you any semblance of a guarantee of a correct count. The other option being a dynamic SQL method building a query based on sys.tables then executing that.
No, from 2005 at least it is the accurate count, no need for the DBCC UPDATEUSAGE.
😎
October 24, 2014 at 9:31 am
A really old way of doing this was to use dbo.sysindexes and using the rowcnt column.
However, this table is only in SQL Server for backwards compatibility - The other queries suggested work nicely.
October 24, 2014 at 10:32 am
Quick thought, sys.partitions is probably the best option as it does not require dynamic sql or depreciated backward compatibility objects.
😎
October 24, 2014 at 3:37 pm
Everyone be aware that this is a school assignment; reposted perhaps to avoid disclosing that fact and get a "complete solution" instead of just helpful tips.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply