April 7, 2014 at 12:30 pm
Comments posted to this topic are about the item Count all records the tables
April 16, 2014 at 4:04 am
you can do it.
select a.name,b.rowcnt
from sysobjects as a inner join sysindexes as b
on a.id=b.id
and a.xtype='u'
and b.indid in (0,1)
April 22, 2014 at 7:25 am
Hi,this is script for same purpose :
set nocount on
declare @nam nvarchar(255)
declare @qur nvarchar(max)
declare shayanCurs cursor for
select top 50 name from sys.objects where type = 'U'
open shayanCurs
fetch from shayanCurs into @nam
WHILE @@FETCH_STATUS = 0
Begin
Select @qur = 'select '''+ @nam +''', count(*) from '+ @nam
Exec (@qur)
print @qur
fetch shayanCurs into @nam
end
deallocate shayanCurs
go
April 24, 2014 at 6:15 am
This is the fastest and most accurate way that I know of doing this:
SELECT
SCHEMA_NAME (T.[schema_id]) AS [schema_name]
,T.name AS table_name
,sqR.total_rows
FROM
sys.tables T
INNER JOIN
(
SELECT
P.[object_id]
,SUM (P.[rows]) AS total_rows
FROM
sys.partitions P
WHERE
P.index_id IN (0, 1)
GROUP BY
P.[object_id]
) sqR ON sqR.[object_id] = T.[object_id]
WHERE
T.is_ms_shipped = 0
AND NOT
(
SCHEMA_NAME (T.[schema_id]) = N'dbo'
AND T.name = N'sysdiagrams'
)
ORDER BY
sqR.total_rows DESC
April 24, 2014 at 8:03 am
without cursor
DECLARE @sql NVARCHAR(max) = N''
SELECT ''['+ltrim(rtrim(sq.name))+'].['+ltrim(rtrim(obj.name))+']'' AS table_name,
COUNT(1)
FROM ['+ltrim(rtrim(sq.name))+'].['+ltrim(rtrim(obj.name))+'] AS row_count
UNION'
FROM sys.objects obj inner join sys.schemas sq on obj.schema_id =sq.schema_id
WHERE obj.type = 'U' and obj.name <>'sysdiagrams'
ORDER by sq.name,obj.name
SET @sql = SUBSTRING(@SQL, 1, LEN(@SQL) - 5)
EXEC (@sql)
April 24, 2014 at 9:47 am
What are the benefits of this script over something simple like the below?
[Code="SQL"]
SELECT st.Name, sp.Rows
from sys.tables st
JOIN sys.partitions sp
on st.object_id = sp.object_id
Order by st.Name
[/Code]
I've ran them both, i'm getting the same rows counts, same tables counts etc. but in half the time...
April 24, 2014 at 9:53 am
Pix1985 (4/24/2014)
What are the benefits of this script over something simple like the below?[Code="SQL"]
SELECT st.Name, sp.Rows
from sys.tables st
JOIN sys.partitions sp
on st.object_id = sp.object_id
Order by st.Name
[/Code]
I've ran them both, i'm getting the same rows counts, same tables counts etc. but in half the time...
The benefit is that counting directly from the table assures the most accurate and up to date numbers. However, there is rarely a large difference (and if so, not for long), so I'd go with your option. It is far less intrusive and much better for resource handling.
April 24, 2014 at 1:38 pm
EXEC sp_msforeachtable 'sp_spaceused "?"'
With this you get the rowcount per table plus the space reserved/used/unused. It works in all versions.
April 24, 2014 at 1:47 pm
pperez 40143 (4/24/2014)
EXEC sp_msforeachtable 'sp_spaceused "?"'With this you get the rowcount per table plus the space reserved/used/unused. It works in all versions.
Great unless you have a lot of tables. 🙁 Here's what I get when I run it:
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
April 24, 2014 at 2:35 pm
How many tables are a lot? Just to know.Well I guess it also depends on the lenghts of the columns.
A workaround would be to output the result to text or to a file. Or insert into a table without displaying in the grid.
April 24, 2014 at 2:37 pm
.
April 24, 2014 at 2:56 pm
pperez 40143 (4/24/2014)
How many tables are a lot? Just to know.Well I guess it also depends on the lenghts of the columns.A workaround would be to output the result to text or to a file. Or insert into a table without displaying in the grid.
Just under 1,000. I just use the system views instead to get these stats, so all good. 🙂
May 27, 2015 at 1:26 pm
Well clearly more than one way to do it.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply