Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Question Unanswered: How to tell if a table is being used.

    Currently I've been assigned the glorious task of cleaning up our database. I've gone ahead and marked a sizable list of tables that I don't believe are accessed anymore. Now, I need to find out whether or not they are being accessed. I've ran sp_depends on all of them to find out what stored procedures/triggers may be accessing them, and the plate is clear. But, there are many individual programs, reports, etc... running against this database, so I'm going to need more proof than that. My boss suggested some sort of SELECT trigger, but that doesn't exist (to what I've found) for SQL Server 2000.

    So, does anyone know how to determine if a table is being accessed or not (other than manually looking through thousands of lines of code)?

    Thanks for any help you can give me.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you happen to have a test system, go ahead and rename the tables, and do a round of testing. Good luck.

  3. #3
    Join Date
    Sep 2003
    Posts
    12
    Originally posted by MCrowley
    If you happen to have a test system, go ahead and rename the tables, and do a round of testing. Good luck.
    I wish it were that simple. There is no test environment that tests all the applications/reports/etc... that are accessing this database. And, if I rename the tables, I've been told that the stored procedures will continue to work properly, while programs executing miscellaneous queries will fail. My initial idea has been to create copies of all the tables that I want to delete (named tablename_DELETE) and drop the original tables. Then if "shit hits the fan", I can rename the backup copy table back to its original form.

    This still puts me in a situation where I have a lot of uncertainty about what I will be affecting. That's why I wanted to find some sort of an access log, or SELECT triger, or something I could use to see if these tables were being accessed.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can periodically look into syscacheobjects table and see if helps.

  5. #5
    Join Date
    Sep 2003
    Posts
    12
    Originally posted by rdjabarov
    You can periodically look into syscacheobjects table and see if helps.
    Good idea. I'm formulating some sort of a job to check this now, but I really curious to know what happens on instances where a table is accessed sparingly. In this case, would an entry exist in the syscacheobjects?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What about using SQL Profiler and running a trace?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Also you could use Profiler for saving tables activity (only selects, inserts, deletes and updates) and analyzing.

  8. #8
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Brett Kaiser
    What about using SQL Profiler and running a trace?
    You are reading my mind....

  9. #9
    Join Date
    Sep 2003
    Posts
    12
    The profiler idea is a good one. I'm going to try that out.

    Thanks for the help!

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    My only qualm about Profiler is that it may not be able to pick up views an/or procedures that access this table. I know sysdepends did not show anything, but how accurate is that?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I sometimes log procedure execution by creating the following table:

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProcedureLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[ProcedureLog]
    GO
    CREATE TABLE [dbo].[ProcedureLog] (
    [ProcessName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ProcessTime] [datetime] NULL ,
    [Application] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CurrentUser] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SystemUser] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[ProcedureLog] WITH NOCHECK ADD
    CONSTRAINT [DF_ProcedureLog_ProcessTime] DEFAULT (getdate()) FOR [ProcessTime]
    GO
    setuser
    GO
    EXEC sp_bindefault N'[dbo].[DF_SYS_APPNAME]', N'[ProcedureLog].[Application]'
    GO
    EXEC sp_bindefault N'[dbo].[DF_SYS_USERNAME]', N'[ProcedureLog].[CurrentUser]'
    GO
    EXEC sp_bindefault N'[dbo].[DF_STRING_EMPTY]', N'[ProcedureLog].[Notes]'
    GO
    EXEC sp_bindefault N'[dbo].[DF_STRING_EMPTY]', N'[ProcedureLog].[ProcessName]'
    GO
    EXEC sp_bindefault N'[dbo].[DF_SYS_SUSERSNAME]', N'[ProcedureLog].[SystemUser]'
    GO
    setuser
    GO

    ...and then putting this code at the start of each procedure I want to monitor:

    --Record the event
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProcedureLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    insert into dbo.ProcedureLog (ProcessName) values ('YourProcedureName')


    This monitors who, what, and when a procedure is called.

    You could put the same code in a trigger on a table.

    Actually, I also include a notes field where I pass the values of all the parameters submitted to the procedure, but this may not be necessary for what you want to do.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, except as the poster said, - there are no dependent ptocedures found for the tables that he wants to research the usage of.

    You could put the same code in a trigger on a table.
    Have they come up with a trigger for SELECT already? Man, the technology is just leaping into the future, leaving us behind every day

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Old technology, actually.

    Just because a relationship doesn't show up in sysdepends doesn't mean it doesn't exist. Dynamic dependencies are not documented in sysdepends.

    And as far as searching for references, maybe you should just script out your database and application code and do string searches for the suspect objects.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Old technology, actually.

    Just because a relationship doesn't show up in sysdepends doesn't mean it doesn't exist. Dynamic dependencies are not documented in sysdepends.

    And as far as searching for references, maybe you should just script out your database and application code and do string searches for the suspect objects.
    Nah....he's worried about applcation connecting to the server...

    There are no TRIGGERS for SELECT btw

    bol

    { [DELETE] [,] [INSERT] [,] [UPDATE] }

    Are keywords that specify which data modification statements, when attempted against this table or view, activate the trigger. At least one option must be specified. Any combination of these in any order is allowed in the trigger definition. If more than one option is specified, separate the options with commas.

    For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Thanks, Brett, that's what I thought

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •