Table information
-
Hi everyone
Hope someone can help out.Could anyone help me with a script to list all the tables and number of rows within a user database.
Had a go but no results achieved.
Apprecaite any help anyone could offer.
Many thanks.
-
Here is a down and dirty way to do it....
Select
SO.NAME,
SI
.Rows
From
Sysobjects SO
INNER
JOIN Sysindexes SI
On
SO.ID = SI.ID
WHERE
SO.Xtype = 'U'
AND
SI.indid in (0,1)
The thing you need to know about this method is that the rows can be outdated, if the indexes have not been updated lately, and that indid of 0 is table without an index while indid 1 is clustered.....they are both included in order to give you tables with no clustered index as well as tables with clustered indexes.......so, while this method will give you fairly accurate rowcounts of tables, do not count on it being 100% accurrate...for that you would need to do a rowcount on each table....
-
thanks for the quick reply , will give it a go
-
hi,
i usually use a 2 stage process , firstly execute the following in query analyser
select "select count (*) '" + name + "' from " + name
from sysobjects
where type = "U"
order by name
this generates something like this :-
select count (*) 'ADDRESS' from ADDRESS
select count (*) 'ADDRESS_COUNTY_MATCHING' from ADDRESS_COUNTY_MATCHING
then copy and paste this into another query analyser session
and you'll get output like this :-
ADDRESS
-----------
26493
ADDRESS_COUNTY_MATCHING
-----------------------
0
adding "set ncount on" before the select removes all annoying (1 row affected ) from the output
hope this helps
Paul
-
Try this.
CREATE PROCEDURE SP_TOM_SPACEUSED (@order int)
AS
set nocount on
if @order not in(0,1)
begin
set @order = 0
end
DECLARE @vcCommand nvarchar(100)
DECLARE @vcName nvarchar(100)
DECLARE @vcName2 nvarchar(100)
CREATE TABLE #tmpTable (
tblname varchar(100)
,vcrows varchar(50)
,vcreserved varchar(50)
,vcdata varchar(50)
,index_size varchar(50)
,unused varchar(50)
)
set @vcName = ' '
WHILE (@vcName is not null)
BEGIN
select @vcName = min(name) from sysobjects
WHERE xtype = 'U'
-- and name like 'TAG_%'
and name > @vcName
IF @vcName is not null
BEGIN
/* Take the ( +", true" ) out of the next line to speed the procedure up */
/* by doing this it might generate incorrect numbers. */
select @vcName2 = b.name +'.'+a.name from sysobjects a join sysusers b
on a.uid = b.uid
where a.name = @vcname and xtype = 'U'
set @vcCommand ='sp_spaceused '+char(39)+ @vcName2 + char(39) + ', true'
print @vcCommand
insert into #tmpTable EXEC sp_executesql @vcCommand
select @vcname2 = ''
END
END
PRINT 'END OF BATCH'
if @order = 0
begin
SELECT * FROM #TMPTABLE
ORDER BY CAST(replace(VCReserved,' kb','') AS INT) DESC
end
if @order = 1
begin
SELECT * FROM #TMPTABLE
ORDER BY tblname
end
--order by tblname
DROP TABLE #TMPTABLE
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply