mark.bienkowski (7/16/2013)
Hello world.I need to show all tables from my database that meet some conditions. For example tables with entries only after a particular date.
I want to see what user tables have been used recently.
Such query gives me the names of tables and the number of entries but how can I add condition on the entires themselves.
select tab.name, i.rows
from dbo.sysindexes i WITH (NOLOCK)
join dbo.sysobjects tab WITH (NOLOCK) on i.id = tab.id
where i.indid in (0, 1)
and tab.xtype = 'U'
--and i.rows <>0
order by tab.name
Please help ladies and gentelmen 😉
At best you can find out what indexes have been used. This is not the same as what tables. So often this type of request is followed up by an explanation that the intention is to delete "unused" tables. Tread lightly here. There are often processes that run only once a year and might fall on your "to be deleted" list.
I am guessing that you want to find some sort of date information with this? Take a look here.
select * from sys.dm_db_index_usage_stats
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/