counting rows in all tables in a database

Hej,
Has anyone a script that will produce a list of all the
tables in a database sorted by the number of rows each
table contains?
thanks,
KevinFor something simple using the estimates instead of actually counting the
rows, try something like
select name, rows from sysobjects
where type = 'U' order by rows desc
For a more costly and more accurate version, you'd have to loop through each
table, select count(*), insert the results into a temp table or table
variable, then select from the temp table..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:05c101c3d379$42411190$a401280a@.phx.gbl...
> Hej,
> Has anyone a script that will produce a list of all the
> tables in a database sorted by the number of rows each
> table contains?
> thanks,
> Kevin|||The script below will give information about the row count and space used
for each table ordered by rowcount and alphabetically. You can choose to
have it run with estimates or up-to-date rowcounts, for which you have to
uncomment DBCC UPDATEUSAGE (0) in the beginning of the script.
I have written a script that provides a lot more information about the data
in your tables, including cardinality and maximum size of the data in a
column, but that will take quite a long time to run.You can find it at:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=629
The script that is attached to this message isn't mine btw, but I can't find
back who the original author is.
--
Jacco Schalkwijk
SQL Server MVP
Script:
SET NOCOUNT ON
-- DBCC UPDATEUSAGE (0)
CREATE TABLE #TBLSize
(Tblname varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @.DBname varchar(80)
DECLARE @.tablename varchar(80)
SELECT @.DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + @.@.ServerName +
' / ' + @.DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType = 'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData,
TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @.tablename
-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @.tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space
(KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc
PRINT ''
PRINT 'By Table Name Alphabetical'
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space
(KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'
DROP TABLE #TblSize
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OPxHQI40DHA.2528@.TK2MSFTNGP10.phx.gbl...
> For something simple using the estimates instead of actually counting the
> rows, try something like
> select name, rows from sysobjects
> where type = 'U' order by rows desc
> For a more costly and more accurate version, you'd have to loop through
each
> table, select count(*), insert the results into a temp table or table
> variable, then select from the temp table..
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> news:05c101c3d379$42411190$a401280a@.phx.gbl...
> > Hej,
> > Has anyone a script that will produce a list of all the
> > tables in a database sorted by the number of rows each
> > table contains?
> >
> > thanks,
> > Kevin
>|||"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:05c101c3d379$42411190$a401280a@.phx.gbl...
> Hej,
> Has anyone a script that will produce a list of all the
> tables in a database sorted by the number of rows each
> table contains?
> thanks,
> Kevin
The following was adapted from SP_SPACEUSED, and will also give the size in
MB.
SELECT
USER_NAME(O.UID) AS OWNER,
O.NAME AS TABLE_NAME,
ROWS = (SELECT I.ROWS FROM SYSINDEXES I WHERE I.INDID < 2 AND I.ID =O.ID),
RESERVED = (CONVERT(DEC(15),(SELECT SUM(I.RESERVED) FROM SYSINDEXES I
WHERE I.INDID IN (0, 1, 255) AND I.ID = O.ID)) / 128),
DATA = (CONVERT(DEC(15),(SELECT SUM(I.DPAGES) FROM SYSINDEXES I WHERE
I.INDID < 2 AND I.ID = O.ID)) / 128),
TEXT = (CONVERT(DEC(15),ISNULL((SELECT SUM(I.USED) FROM SYSINDEXES I
WHERE I.INDID = 255 AND I.ID = O.ID),0)) / 128),
USED = (CONVERT(DEC(15),(SELECT SUM(I.USED) FROM SYSINDEXES I WHERE
I.INDID IN (0, 1, 255) AND I.ID = O.ID)) / 128)
FROM SYSOBJECTS O
WHERE O.TYPE = 'U'
Mike Kruchten|||http://www.aspfaq.com/2428
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:05c101c3d379$42411190$a401280a@.phx.gbl...
> Hej,
> Has anyone a script that will produce a list of all the
> tables in a database sorted by the number of rows each
> table contains?
> thanks,
> Kevin