Modify TSQL Query to include last modified data on Tables

I did not write the SQL script below but how would it be modified to include a column hat list that last modified time stamp on all tables.

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
FROM #temp a INNER JOIN information_schema.columns b ON a.table_name
collate database_default = b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) 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.

_agx_Commented:
When you say "modified", do you mean when was the last insert/delete/update? Last I checked, that was only partially possible. sys.dm_db_index_usage_stats can tell you the last modified date for a table, BUT .... only since the last server restart.  To track the overall date permanently, you need to DIY which isn't trivial.

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.



Edit: Added example

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(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Open in new window

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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> include a column hat list that last modified time stamp on all tables.

When you say Last modified time stamp, kindly clarify whether it indicates INSERT/UPDATE/DELETE operations or else you are looking for SELECT operations on a table as well.
Unless otherwise you have a MODIFIED_Datetime column available across all your tables, you will not be able to identify the exact modified date time because of the restrictions mentioned by agx above.
Moreover,  sys.dm_db_index_usage_stats will capture information for any index modifications only and it will not have track of any column changes which is not part of any indexes.
So, it might not be 100% accurate and if you want partial or rough details, then you can use the query provided by agx above.
Mark WillsTopic AdvisorCommented:
I think you want sys.tables
select name,create_date,modify_date from sys.tables

Open in new window


If that suits, then let us know and we can tidy up that ms_foreachtable to use a better approach
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mark WillsTopic AdvisorCommented:
While I was waiting, thought I would run through an example....
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 'exec sp_spaceused ''?'''

;with CTE_Table as
(select '['+s.name+'].'+'['+t.name+']' as table_name, s.name as [schema], t.name as [table], t.create_date,t.modify_date
        ,(select count(*) from sys.columns c where object_name(c.object_id) = t.name) as column_count
 from sys.tables t
 inner join sys.schemas s on t.schema_id = s.schema_id
)SELECT a.table_name,t.[schema],t.[table],t.create_date,t.modify_date, a.row_count, t.column_count, a.data_size
from  #temp a 
inner join  CTE_Table t on a.table_name = t.table_name
order by right('00000000'+data_size,10) desc


DROP TABLE #temp

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Hi Mark,
Hope you are fine, seeing you after a long time..
Modify_date in sys.tables will list only when the TABLE structure was last modified and not about the data and hence I believe this might not help the author on his requirement.
Even the above one on sys.dm_db_index_usage_stats is with respect to Index modifications and hence focus only on the columns involved in the indexes. So, it would not track any modifications that happened to columns not part of the index..
Mark WillsTopic AdvisorCommented:
G'day Raja,

Yep, has been a long time :)

And maybe my absence has made my mind a bit mushy because I didnt read it as data movement (select/insert/update/delete), but structural.

I guess the underlying question is what does "that last modified time stamp"  actually mean :)

If wanting to get an indication of unused tables, that is unfortunate...

And of course that query would need to be
SELECT convert(varchar(255), t.name) AS [Table], SUM(i.user_seeks + i.user_scans + i.user_lookups) AS Total_accesses
FROM sys.dm_db_index_usage_stats i 
inner join sys.tables t ON (t.object_id = i.object_id)
GROUP BY i.object_id, t.name 
ORDER BY [Total_accesses] DESC

-- but in terms of usage [total_accesses] it is only relevant since :

SELECT create_date 
FROM sys.databases
WHERE name = 'TEMPDB'

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I guess the underlying question is what does "that last modified time stamp"  actually mean :)

yes, that's correct. If the author can give more details, then we can help him out better..
compdigit44Author Commented:
Wow thank you all for the great feedback. In regards to last modified time stamp. I am referring to the last time data was updated in the table and sort the dates from newest to oldest
Mark WillsTopic AdvisorCommented:
>> last time data was updated in the table

Ouch.... As my esteemed colleagues have suggested - and posted - that aint easy :)

Did you try _agx_ solution - the first post above at #a42519939 ?

As Raja and _agx_ have already said... Be very much aware that the data contained in Dynamic Management Views (DMV) has limited scope - typically since last restart. So, be very careful if intending to act on them.

Please read : https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql
compdigit44Author Commented:
Thanks from the TSQL script you posted, how would I sort the data columne by newest to oldest.
_agx_Commented:
Replace the ORDER BY size

          ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

... with the new date column:

                ORDER BY s.last_user_update DESC

Though you might want to add a secondary sort column, in case the date is null. Like sort by date, then "size"

           ORDER BY s.last_user_update DESC, CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
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.