November 18, 2008 at 6:54 am
Hi,
I hope you can help me.
I want a write a stored procedure that returns the number of records in a selected tables
Say for example, I have the database db1 and I have so many tables in that those are t1,t2,....t10. Now I want a SP that should return the row count of t1, t4, t6 and t7.
Is it possible in SQL Server 2005?
Thank You
November 18, 2008 at 7:07 am
thre's two ways to do this;
the sysindexes table has a rowcount column in it, but it is possible that it is not quite accurate; it's a good approximation of the nubmer of rows, and because it is already materialized, it is very fast.
other than that, you'd need to actually select count(*) from each table, which is slower...in your example, with only 4 specific tables, you coudl do that.
here's something i have saved in my snippets to use the indexes to count the rows for ALL tables.
[font="Courier New"]
CREATE PROCEDURE sp__CountTableRows
AS
BEGIN
SELECT sysobjects.name, MAX(sysindexes.rows) AS NUMROWS
FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE sysobjects.xtype = 'U'
--your example for specific tables: AND sysobjects.name in('t1','t4','t6','t7')
GROUP BY sysobjects.name
ORDER BY NUMROWS DESC,sysobjects.name
END[/font]
Lowell
November 18, 2008 at 7:16 am
to do a count(*) for each table you can use;
sp_MSforeach table
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select * from #rowcount
order by tablename
drop table #rowcount
and use a WHERE condidtion to include just the tables that you want
November 18, 2008 at 7:17 am
Thanks for you reply Lowell.
I am try both the solutions. I will prefer the accurate one.
Accuracy is more priority than performance so the SP must have to provide the accurate result.
Thank You
November 18, 2008 at 7:20 am
steveb (11/18/2008)
to do a count(*) for each table you can use;sp_MSforeach table
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select * from #rowcount
order by tablename
drop table #rowcount
and use a WHERE condidtion to include just the tables that you want
Thanks for you reply Steveb.
Your solution is look good. I will check this also.
Thank You
November 18, 2008 at 7:47 am
yeah it depends on how accurate your data needs to be; a developer might just need to know whether some table has a lot of rows or not, but other situations need exact counts.
i merged the two datasets together witht eh code below, the results varied from database to database as to which tables did not match the index approximations. as an FYI, when you rebuild the indexes, they are 100% accurate, and vary over time as inserts and deletes occur until the next rebuild.
[font="Courier New"] SELECT sysobjects.name, MAX(sysindexes.rows) AS NUMROWS
INTO #TMP1
FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE sysobjects.xtype = 'U'
GROUP BY sysobjects.name
ORDER BY NUMROWS DESC,sysobjects.name
CREATE TABLE #rowcount (tablename VARCHAR(128), rowcnt INT)
EXEC sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
SELECT * FROM #rowcount
ORDER BY tablename
UPDATE #rowcount SET tablename=REPLACE(tablename,'[dbo].[','')
UPDATE #rowcount SET tablename=REPLACE(tablename,']','')
SELECT * FROM #rowcount
INNER JOIN #TMP1 ON #rowcount.tablename = #TMP1.name
WHERE #rowcount.rowcnt <> #tmp1.numrows
ORDER BY numrows DESC[/font]
Lowell
November 20, 2008 at 6:18 am
Lowell (11/18/2008)
yeah it depends on how accurate your data needs to be; a developer might just need to know whether some table has a lot of rows or not, but other situations need exact counts.i merged the two datasets together witht eh code below, the results varied from database to database as to which tables did not match the index approximations. as an FYI, when you rebuild the indexes, they are 100% accurate, and vary over time as inserts and deletes occur until the next rebuild.
Thank you very very much Lowell. I will check two possibilities and proceed.
Thank You
November 20, 2008 at 6:24 am
Failing to plan is Planning to fail
November 20, 2008 at 7:29 am
I'm not sure why do you want to run SP, till you can run this script and see some results anywhere in your DB, try it:
SELECT o.name TableName ,i.rows TblRowCount
FROM sysobjects o
INNER JOIN sysindexes i ON (o.id = i.id)
WHERE o.xtype = 'u'
AND i.indid < 2
November 20, 2008 at 7:33 am
it's sure that you can find many ways one above and this one for is the best:
USE Your_Database;
GO
SELECT o.name AS "Table Name", i.rowcnt AS "Rows"
FROM sysobjects o, sysindexes i
WHERE i.id = o.id
AND indid IN(0,1)
AND xtype = 'u'
AND o.name <> 'sysdiagrams'
ORDER BY i.rowcnt DESC
November 20, 2008 at 12:06 pm
For SQL Server 2005, you can use the following query:
SELECT t.name AS table_name, i.name AS index_name, SUM(ps.row_count)
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.index_id IN (0,1)
GROUP BY t.name, i.name
ORDER BY t.name
The sys.dm_db_partition_stats dynamic management view has some other interesting information in it too like in-row vs. LOB vs. overflow page counts.
December 5, 2008 at 7:48 am
Thanks to all of you for your better suggestions
I need SP because I need to send the mail with the table counts for selected tables in a database. This SP should run once a day at night 9 PM. It needs to send the row count of selected tables.
for example, If the database have t1,t2,t3.... t10 tables.
I need the row count as ___
t2 3000
t4 10000
t5 300
t8 5000
The same I need to keep in body of mail and needs to sent to a mail. I have a SP to send the mail. So I want to take this result and give to cursor and prepare a body.
Does it make sense?
Thank You
December 5, 2008 at 8:27 am
venki (12/5/2008)
Thanks to all of you for your better suggestionsI need SP because I need to send the mail with the table counts for selected tables in a database. This SP should run once a day at night 9 PM. It needs to send the row count of selected tables.
for example, If the database have t1,t2,t3.... t10 tables.
I need the row count as ___
t2 3000
t4 10000
t5 300
t8 5000
The same I need to keep in body of mail and needs to sent to a mail. I have a SP to send the mail. So I want to take this result and give to cursor and prepare a body.
Does it make sense?
We need to consider the schema information also
dbo, user1, user2....
I need the
user1.t2 3000
user1.t4 10000
user1.t5 500
user1.t8 5000
Thank You
December 5, 2008 at 8:38 am
venki (12/5/2008)
I have a SP to send the mail. So I want to take this result and give to cursor and prepare a body.Does it make sense?
I'm not sure that it does to me anyway. The easiest way to send query results in SQL Server 2005 by e-mail is to use database mail and EXEC msdb.dbo.sp_send_dbmail
http://msdn.microsoft.com/en-us/library/ms190307(SQL.90).aspx
If you're using some other custom built procedure to send the e-mail, then we probably can't help you without knowing how that stored procedure works and what its parameters are.
December 5, 2008 at 8:53 am
Chris Harshman (12/5/2008)
If you're using some other custom built procedure to send the e-mail, then we probably can't help you without knowing how that stored procedure works and what its parameters are.
My DBA has created one SP and that takes 4 parameters
FROM - from mail ID
TO - to mail ID
SUBJECT
BODY
Is there any system table that contains the schema and the table details? then we can join them and get it?
Thank You
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply