Hoping you can help me here... I have a situation where I need to provide
a
script that can be run through query analyzer to return the number of record
s
in each table in my DB.
I would like it to be dynamic, as the number of tables in the DB changes.
Pretty simple query (Select count(*) from tblXXX), but I can't seem to find
where the actual table names are stored within the system tables so that I
can run through all of them.
I know that I could use Enterprise Manager and view the DB as a TaskPad to
see the number of records in each table, but the person who will be doing
this only has permissions to view the DB through QA.
Any thoughts on a script that will do this?
Thanks in advance...
gTry,
use northwind
go
-- undocumented sp
exec sp_msforeachtable 'select ''?'', count(*) from ?'
go
-- using sysindexes
dbcc updateusage (0)
select object_name([id]), rowcnt
from sysindexes
where indid in (0, 1) and objectproperty([id], 'IsUserTable') = 1
order by 1
go
-- using a cursor and dyanmic sql
create table #t (
table_name sysname,
rowcnt int,
)
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare my_cursor cursor
local
fast_forward
for
select
table_schema,
table_name
from
information_schema.tables
where
table_type = 'BASE TABLE'
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + ''' as table_name, count(*) as rowcnt from
[' + @.ts + '].[' + @.tn + ']'
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t order by table_name
drop table #t
go
AMB
"Greg Toronto" wrote:
> Hoping you can help me here... I have a situation where I need to provid
e a
> script that can be run through query analyzer to return the number of reco
rds
> in each table in my DB.
> I would like it to be dynamic, as the number of tables in the DB changes.
> Pretty simple query (Select count(*) from tblXXX), but I can't seem to fin
d
> where the actual table names are stored within the system tables so that I
> can run through all of them.
> I know that I could use Enterprise Manager and view the DB as a TaskPad to
> see the number of records in each table, but the person who will be doing
> this only has permissions to view the DB through QA.
> Any thoughts on a script that will do this?
> Thanks in advance...
> g|||In SQL Server 2000:
DBCC UPDATEUSAGE(0);
select o.name, i.rowcnt
from sysobjects o
inner join sysindexes i
on o.id = i.id
WHERE i.indid IN (0,1)
AND OBJECTPROPERTY(o.id, 'IsMsShipped') = 0
ORDER BY o.name
In SQL Server 2005, it's a little more complex, accounting for table
partitions:
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Greg Toronto" <GregToronto@.discussions.microsoft.com> wrote in message
news:1AEB569D-DA79-4B7B-9892-932B37D4823E@.microsoft.com...
> Hoping you can help me here... I have a situation where I need to
> provide a
> script that can be run through query analyzer to return the number of
> records
> in each table in my DB.
> I would like it to be dynamic, as the number of tables in the DB changes.
> Pretty simple query (Select count(*) from tblXXX), but I can't seem to
> find
> where the actual table names are stored within the system tables so that I
> can run through all of them.
> I know that I could use Enterprise Manager and view the DB as a TaskPad to
> see the number of records in each table, but the person who will be doing
> this only has permissions to view the DB through QA.
> Any thoughts on a script that will do this?
> Thanks in advance...
> g
