All Forums Database
eric_td 10 posts Joined 04/13
19 Feb 2016
Link the Database Name/TableName to DatabaseID/TableId

Hi,
My requirement is below could you please help me with this.
I have a set of eg 10 tables; for each of the 10 tables, I need to get the stats collected on them.
 
1. Basically I get the tables created in past one week from DBC.Tables.
2. For each of these tables I need to get the statistics collected for them.
 

I have tried picking statistics records from DBC.StatsTbl but this table has Database Id /Object_id(not db/tbl name). How can i join the tables from dbc.tables to dbc.statstbl.

 

DBC.Tvm contains DBid/Tbl id but contains only TableName, not databasename. So I am not able to make an inner join.

 

Can you please help me here.
Thanks
Eric.

dins2k2 51 posts Joined 05/13
19 Feb 2016

Eric,
 
Stats details can be fetched from DBC.StatsV view and it has almost all stats details of the objects. If you still wanna get the details from DBC.StatsTbl, try the below query(change the where condition accordingly)
 

Select	d.DatabaseNameI, t.TVMNameI, s.*
  from dbc.DBase d join dbc.TVM t 
	on d.databaseid=t.databaseid
join dbc.StatsTbl s 
	on t.tvmid=s.objectid
where d.DatabaseNameI=<database_name> 
       and t.TVMNameI=<table_name>

 
HTH.
 
Thanks,
Dinesh

eric_td 10 posts Joined 04/13
22 Feb 2016

Makes sense  to use StatsV , Dinesh -Thanks a lot :) 

You must sign in to leave a comment.