Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Which table is growing??

Author  Topic 

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-04-30 : 09:16:35
Hi,

My friend has a database that has an external program inserting data into it, there are many tables and a lot of data being inserted. but we do not know which table is having the data inserted?

I need a quick and easy way to find this out it is urgent, can anyone help/give instruction?

Many thanks!! help greatly appreaciated....

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-30 : 11:48:14
Use one or the other of the following. Second query is from here: http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

SELECT object_name(object_id),SUM (row_count) , SUM(in_row_data_page_count) AS page_count
FROM sys.dm_db_partition_stats
where (index_id=0 or index_id=1)
group by object_name(object_id) order by 2 DESC

SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, p.Rows
ORDER BY
t.Name
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-30 : 11:53:31
Can use this to see # rows in each table
select	t.name, p.rows
from sys.partitions p
join sys.tables t
on t.object_id = p.object_id
where index_id < 2


or this to see # rows and size
sp_msforeachtable
'EXEC sp_spaceused ''?'''


But to really see what's going on you likely need to run a trace

Go to Top of Page
   

- Advertisement -