Solved

SQL 2008 R2 Query to List All Databases & Tables with Last Access Date and Time

Posted on 2013-06-11
12
1,870 Views
Last Modified: 2013-07-12
My SQL knowledge is very limited, but I need a query to list all databases, the tables in each database, as well as the last access dates and times.  This is for purposes to present which tables and databases are stale and no longer in use and can be disconnected (some were created temporarily for restore purposes, but need to verify which aren't the active ones)

I know enough to be able to paste whatever code you provide and will generate these results for me so that I can export them to an excel file to provide the user.

Thanks.
0
Comment
Question by:fireguy1125
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 4

Expert Comment

by:TalShyar
ID: 39238568
Please go to "Original Article" for additional details.

Couple of problems with what you are asking:
1. The code below will on ly work in SQL 2005 and above
2. If SQL Server service is restarted, then all the data will be reset
3. If you have Full Text catalogs turned on, then you may see an access time recorded by it.

SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT 
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2

Open in new window

0
 
LVL 1

Author Comment

by:fireguy1125
ID: 39238577
Thanks, sorry, can you also include the parameter to show the size of each table as well.
0
 
LVL 4

Expert Comment

by:TalShyar
ID: 39238782
Try this:

NOTE: Be sure to replace "TestDB" with the name of your DB. Unfortunately, you will have to run the code below a single database at a time.

Use TestDB;

WITH LastActivity (ObjectID, LastAction)
AS
(
	SELECT object_id AS TableName, Last_User_Seek as LastAction
	FROM sys.dm_db_index_usage_stats u
	WHERE database_id = db_id(db_name())
UNION
	SELECT object_id AS TableName,last_user_scan as LastAction
	FROM sys.dm_db_index_usage_stats u
	WHERE database_id = db_id(db_name())
UNION
	SELECT object_id AS TableName,last_user_lookup as LastAction
	FROM sys.dm_db_index_usage_stats u
	WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
MAX(la.LastAction)as LastSelect
FROM sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
	WHERE 
		so.type = 'U'
		AND so.object_id > 100 
	GROUP BY OBJECT_NAME(so.object_id)
	ORDER BY OBJECT_NAME(so.object_id)

Open in new window

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39238790
Unless you have built in some support for the "Last Access Date and Time" you are simply up the proverbial creek.  In other words it cannot be done.
0
 
LVL 1

Author Comment

by:fireguy1125
ID: 39239075
Is there any way query to perform this for all databases? Or a way to include  and list all the databases in a single query, if so can you please provide updated query for names such as Database1, Database2, Database3, etc - I can fill in the rest of the info.
0
 
LVL 4

Expert Comment

by:TalShyar
ID: 39239124
There probably is but all the solutions I have tried are not working consistently. I am not at a place that I can test for now. If I can get it working, I will post it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39239739
Not to burst your bubble but the DMV sys.dm_db_index_usage_stats will not produce the answer you need.  Here is the part you are overlooking:
The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

And again there is no magic bullet for a poorly designed of badly documented database.
0
 
LVL 1

Author Comment

by:fireguy1125
ID: 39239769
TalShyer:

I only see Colums TableName and LastSelect, which seems to be fine, just need the size of each table though also.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39240735
Be cautious please:
Please take into account that there simply is no automatic method of doing what you ask. There are approaches to gathering some helpful information - but you have to be able to interpret the results.

Then take into account that some tables may not be accessed for "good reason" but still be essential to an overall solution (e..g some might be accessed infrequently "by design" and/or "by their nature".

To begin to address this request you would have to start collecting metrics - over time. In particular you could not expect to take a snapshot of the last few hours and extrapolate that some tables are redundant.

Tables that have been specifically designed to include information such as 'last updated date' are a much more reliable source of the needed metrics here - but all too infrequently tables don't hold such information.

So, please don't expect a single query to provide all the information you need - and just because a table hasn't been accessed in a short period does not make it redundant.
0
 
LVL 1

Author Comment

by:fireguy1125
ID: 39241800
Ok, if it can't be done in one query, i guess I would have to go to each of my 30 databases and run separate queries on each one, which for this purpose is fine. So in essence I need queries for:

-list tables for database
-provide last access date/time for each table in database
-provide size for each table in database

also, what would be the best process to export this data in some type of spreadsheet or csv file

Basically the developers would like this information so they can go through and check what they use, and in the case of the temporary databases would determine which ones are no longer needed.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39242696
Ok, if it can't be done in one query, i guess I would have to go to each of my 30 databases and run separate queries on each one, which for this purpose is fine.
No, you are missing the point: It cannot be done reliably unless you have it built in. Period.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39243484
for getting tables sizes, many approaches exist, several are discussed here where most approaches move through each database and each table using sp_spaceused (take note of the remarks of that sp). This same discussion includes notes such as:
If you are using SQL Server Management Studio, instead of running a query (which in my case returned duplicate rows) you can run a standard report.

Right click on the database
Navigate to Reports > Standard Reports > Disk Usage By Table
Note: The database compatibility level must be set to 900 or above for this to work correctly. See http://msdn.microsoft.com/en-gb/library/bb510680.aspx
i.e. it might not be necessary for you to run scripts for this information.

So: gathering table sizes is quite possible and can be run across multiple databases. (but there are some caveats about the accuracy/currency of that data to be aware of).

>>Basically the developers would like this information so they can go through and check what they use, and in the case of the temporary databases would determine which ones are no longer needed.
I remain very dubious about the wisdom/relevance of the "last accessed" information you are also seeking if derived from sys.dm_db_index_usage_stats - however that choice is yours to make.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question