TSQL Query Showing Duplicate Results

When I run the TSQL query below, it list each table twice both with different last modified time stamps. How can this be changed to only show the most recent update time.

CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50),
index_size VARCHAR(50), unused_size VARCHAR(50))
SET NOCOUNT ON INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size, s.last_user_update
FROM #temp a INNER JOIN information_schema.columns b ON a.table_name
collate database_default = b.table_name collate database_default
	LEFT JOIN sys.dm_db_index_usage_stats s ON s.OBJECT_ID = OBJECT_ID(b.TABLE_NAME)
GROUP BY a.table_name, a.row_count, a.data_size, s.last_user_update
ORDER BY CAST(REPLACE(s.last_user_update, '', '') AS date) DESC
DROP TABLE #temp

Open in new window

LVL 21
compdigit44Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
SELECT a.table_name, a.row_count, b.col_count, a.data_size, s.last_user_update
FROM #temp a
CROSS APPLY (
    SELECT COUNT(*) AS col_count
    FROM sys.columns
    WHERE object_id = OBJECT_ID(a.table_name)
) AS b
OUTER APPLY (
    SELECT TOP (1) *
    FROM sys.dm_db_index_usage_stats s2
    WHERE s2.object_id = OBJECT_ID(a.table_name)
    ORDER BY s2.last_user_update DESC
) AS s
ORDER BY s.last_user_update DESC, a.table_name
DROP TABLE #temp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
compdigit44Author Commented:
I am getting the following error " Invalid object name '#temp'."
0
Scott PletcherSenior DBACommented:
Sorry, I didn't change the part before the SELECT so I didn't repeat it in my code, but it is still needed:

CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50),
index_size VARCHAR(50), unused_size VARCHAR(50))
SET NOCOUNT ON INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT ...
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

compdigit44Author Commented:
that worked. I am trying to run this query to compare a production DB and DR DB that uses log shipping to ensure both DB's are insync with data
0
compdigit44Author Commented:
What I am finding interesting if the fact the DR server which is in standby mode with log shipping showing updates table modified dates for the first 10 table but list the rest as NULL. yet when I do a dump on  the table do see data in there but un sure of it time stamp
0
Scott PletcherSenior DBACommented:
The modified datetime is only since the server (instance) has been started.  SQL doesn't store that history anywhere.  If you need history, you need to capture it periodically yourself.
0
compdigit44Author Commented:
But you would think if some table has modified time stamps others would as well
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.