Solved

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

Posted on 2013-06-11
12
1,702 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 48

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 48

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Mirror and Replication 5 18
Numeric sequence in SQL 14 38
Group by and order by clause 28 36
How to simplify my SQL statement? 14 23
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now