Geek Answers Handbook

Is there an equivalent “Coverage Code” for SQL databases?

I have a database with many tables used and many tables that are no longer in use. Although I could sort each table manually to make sure they are still in use, this would be a cumbersome task. Is there any software / hidden feature that can be used in the SQL Server / Oracle database that will return information such as "Tables x, y, z were not used last month" "Tables a, b , c have been used 17 times today? " Or perhaps a way to sort tables using "Last Modified / Selected From"?

Or is there a better way to do this? Thanks

edit: I found the "modify_date" column while executing "SELECT * FROM sys.tables ORDER BY modify_date desc", but this seems to be tracking changes to the table structure, not its contents.

+5
source share
6 answers

replace the spt_valuestablename with the name you are interested in, the query will give the last time it was used, and that it was used

From here: How to find out how many times a table is used in special or procedural calls in SQL Server 2005 and 2008

SELECT * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,execution_count,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
         THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement like '%spt_values%'  -- replace here
AND sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
ORDER BY execution_count DESC

Keep in mind that if you restart the window, it will be cleared

+6
source

Oracle ASH ( ), SQL, . , , .

, DBA_TAB_MODIFICATIONS. , , , . , DBA_TAB_MODIFICATIONS. , . . , ASH.

, ASH . ( , ).

+1

, .

, , .

0

/, SQL Server, , .

0

, .

select * from sys.dm_db_index_usage_stats

select * from sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL)

select * from sys.sql_expression_dependencies /*SQL Server 2008 only*/

, DMVs .

0

, SQL Cover, , tSQLt.

0

More articles:


All Articles