CODEWITHSUNDEEP
sql-serverazuret-sqlazure-sql-databaseazure-synapse
bmsqldev
bmsqldev

Reputation: 2735

Table Details in Azure Datawarehouse

Is there any in-built query/catalog view available in Azure Data warehouse to show last updated date, last inserted date, last deleted date, last index scanned date of a table? I tried below query

SELECT
tbl.name
,ius.last_user_update
,ius.user_updates
,ius.last_user_seek
,ius.last_user_scan
,ius.last_user_lookup
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
FROM
sys.dm_pdw_nodes_db_index_usage_stats ius INNER JOIN
sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
WHERE ius.database_id = DB_ID()

But it is not giving output against all the tables. Any help much appreciated.

Upvotes: 0

Views: 592

Answers (2)

Gidi9
Gidi9

Reputation: 222

The closest I could find for Azure Synapse Data warehouse was this query. Only lets you know when last the schema changed (as mentioned here):

SELECT name, [modify_date] FROM sys.tables
where name like '%YOUR-TABLE-NAME%'

Upvotes: 1

Utkarsh Pal
Utkarsh Pal

Reputation: 4544

Use sys.dm_db_index_usage_stats instead of sys.dm_pdw_nodes_db_index_usage_stats in your query and it will work fine.

I'm getting the required output:

enter image description here

If you want to find out when was the last table updated, you can query dynamic management view (DMV) – sys.dm_db_index_usage_stats and easily figure out when was the table updated last, try the below query:

SELECT OBJECT_NAME(OBJECT_ID) AS TableName,
 last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( '<database_name>')
AND OBJECT_ID=OBJECT_ID('<table_name>')

Change <database_name> and <table_name> with your values.

Upvotes: 1

Related Questions