Link the Database Name/TableName to DatabaseID/TableId

Database
Enthusiast

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.

2 REPLIES
Enthusiast

Re: Link the Database Name/TableName to DatabaseID/TableId

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

Enthusiast

Re: Link the Database Name/TableName to DatabaseID/TableId

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