SQL Server Performance

SQL Server table usage

Discussion in 'SQL Server 2005 General DBA Questions' started by ismailadar, Dec 15, 2010.

  1. ismailadar New Member

    hi all,
    I have a database in sql server 2000 and i suspect that some tables are nor used anymore and i wanto to drop them. but before drop tables how can i check whether they are used or not?
    thanks in advance
    ismailadar, Dec 15, 2010
    #2

  2. Luis Martin Moderator

    You can create a trigger for that tables. This trigger could write in some table if table was used.
    After some time you check what table was not used.
    Luis Martin, Dec 16, 2010
    #3

  3. ismailadar New Member

    i can track only dml changes by using triggers. How can i track select statements aginst this tables?
    ismailadar, Dec 16, 2010
    #4

  4. Luis Martin Moderator

    You are wright. I'm afraid there is nothing to do for select.
    Luis Martin, Dec 17, 2010
    #5

  5. FrankKalis Moderator

    When you only use procedures to access the data, you could search syscomments (if I recall correctly) to find references to that tables. If you however also use embedded SQL in the client applications, or even allow to send dynamically created statements to the database, you could ask the client developer whether or not they reference these tables in their code (and trust them...) or as the hard way you rename the table and see if something breaks.
    FrankKalis, Dec 17, 2010
    #6

  6. satya Moderator

    Ismail
    Do you have any storage free space issues on this instance?
    If not I wouldn't recommend the tables that you feel they are not used. As referred you can run a server side trace to find whether thes objects are accessed, there is no quick way to get this out but a brief planning is required to identify the tables.
    satya, Dec 21, 2010
    #7

  7. ismailadar New Member

    thank for your replies, Firstly i will trace objects and i will change the name and i will test system whether any error appears or not after that i would bbe ensure that object not used and i will back up db and i will delete the object.
    ismailadar, Dec 21, 2010
    #8

  8. satya Moderator

    ...just another step after the backup is to create & store a TSQL script for dropped tables. Just in case if you need to use means you can simply use the scrip to create and restore database as another name to import the data [:)]
    satya, Dec 21, 2010
    #9

  9. amit.yadav2 New Member

    amit.yadav2, Jan 10, 2011
    #10

Share This Page