Friday, June 19, 2015

get numbers of table in database used by front end application or any user

sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
ORDER BY last_user_seek DESC
Folowing command will tell the procedure name executed

SELECT TOP 1000 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
    d.cached_time, d.last_execution_time, d.total_elapsed_time,
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
    d.last_elapsed_time, d.execution_count
,DB_NAME(d.database_id) AS [SCHEMA_NAME]
FROM sys.dm_exec_procedure_stats AS d
where DB_NAME(d.database_id) = 'databaseName'
ORDER BY [last_execution_time] DESC;

No comments: