SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Which table is growing??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mitin
Yak Posting Veteran

81 Posts

Posted - 04/30/2013 :  09:16:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 04/30/2013 :  11:48:14  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 04/30/2013 :  11:53:31  Show Profile  Visit russell's Homepage  Reply with Quote
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


Edited by - russell on 04/30/2013 11:54:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000