locked
last access date of fields in sql server table RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello All,

    is there any queries to find last access date of all fields in sql server tables.

    because in our database table having no of field,most of fields are unused from long time.we want drop those fields from tables to make the table light.

    Thanks In Advance!!!

    mastanvali shaik

    • Moved by Kalman Toth Tuesday, August 19, 2014 7:00 AM Not database design
    Tuesday, August 19, 2014 6:14 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Do the fields have indexes?

    In SQL Server 2005 you can use the sys.dm_db_index_usage_stats data 
    management view, look at the column last_user_update, you can also see when 
    the table was last accessed (last_user_seek and last_user_scan - do a MAX on 
    them).

    SELECT
        last_user_seek = MAX(last_user_seek),
        last_user_scan = MAX(last_user_scan),
        last_user_lookup = MAX(last_user_lookup),
        last_user_update = MAX(last_user_update)
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        [database_id] = DB_ID()
        -- if you want to leave out system objects, uncomment the next line:
        -- AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0


    --last modified date

    select name,modify_date from sys.procedures
    order by modify_date desc

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, August 19, 2014 1:25 PM
    Answerer

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    No, SQL Server don't log (audit) such informations by default, you have to implement an audit to get such informations.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Kalman Toth Tuesday, August 19, 2014 6:59 AM
    Tuesday, August 19, 2014 6:18 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    for example i have 300 fields in a table  using audit may i know how to find  fields last access date. 

    mastanvali shaik

    Tuesday, August 19, 2014 6:45 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    If you haven't implemented an Audit yet, then you can not get this information.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 19, 2014 6:56 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    ok i will try this and update it.

    mastanvali shaik

    Tuesday, August 19, 2014 8:52 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Do the fields have indexes?

    In SQL Server 2005 you can use the sys.dm_db_index_usage_stats data 
    management view, look at the column last_user_update, you can also see when 
    the table was last accessed (last_user_seek and last_user_scan - do a MAX on 
    them).

    SELECT
        last_user_seek = MAX(last_user_seek),
        last_user_scan = MAX(last_user_scan),
        last_user_lookup = MAX(last_user_lookup),
        last_user_update = MAX(last_user_update)
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        [database_id] = DB_ID()
        -- if you want to leave out system objects, uncomment the next line:
        -- AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0


    --last modified date

    select name,modify_date from sys.procedures
    order by modify_date desc

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, August 19, 2014 1:25 PM
    Answerer