Solved

How to identify tables that are not performed insert or update operation?

Posted on 2013-05-09
6
244 Views
Last Modified: 2013-05-22
In my database there a table called Storage. This table is used for only reference in SPs, FKs  and UDFs. But there is no insert, update and delete operation performed on this table.

Is there any easy way to identify this kind of tables in my database? If so, please do share with me.

Please do assist.
0
Comment
Question by:Easwaran Paramasivam
6 Comments
 
LVL 20

Expert Comment

by:Marten Rune
ID: 39156293
I don't understand your question. Can you please clarify.

Do you want to find tables, that dont have any inserts, deletes or updates, or do you want to reference it in a simple way. Or ???

Please elaborate a Little, what, why and so forth

Regards Marten
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 333 total points
ID: 39157016
Hi,

Use the standard reports from SSMS to list the Disk Usage by Table (Right click on database, select Reports, select Standard Reports ... )

Store that result. I suggest exporting to excel.

Time later, do the above again.

Compare.

Tables with the #Records the same are close to your definition ...

Otherwise, add a timestamp column to all tables.

In another table store the max( timestampcolumn) value for each table.

Occasionally check the stored value against current calculated value. If the value has increased, then a something has been performed on that table.

HTH
  David

PS Cant store max( timestampcolumn ) in a column of type timestamp. Its some kind of int
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39159105
I would like to identify tables that are not modified by insert and delete statement. There could be update statement.

I look for all tables above kind in the given database.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39159659
Simply put, if you do not have any provisions for this in your code, there is no way to know when data was changed.  The best you can do is search all your code for the word UPDATE and even then it does not account for ad-hoc queries.
0
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 167 total points
ID: 39159823
Well there is other options. I e, actually read the log, and see whats happening.
Create triggers on every table that logs to a logtable what was changed and when.
Run a profiler and examine it later.
And MS new option Change tracking. Not familiare with this one yet, but it sounds as if it could provide the information you're after. Look at:
http://msdn.microsoft.com/en-us/library/bb964713.aspx

Thats all I can Think of.
The first, read the log needs 3'rd party Tools, or a dedicated and skilled SQL Admin.
Triggers will put a BIG overhead, monitor server carefully.
Profiler, also puts a overhead, monitor carefully.
And the Change Tracking is somewhat a blank page to me. But I would assume its going to put a big overhead on your server. Mostly because of I/O.

Regards Marten
0
 
LVL 35

Accepted Solution

by:
David Todd earned 333 total points
ID: 39159871
Hi,

The timestamp idea I posted above will show updates and inserts, but not deletes. The reason is that the row with the changed timestamp value is no longer in the table ...

Some of the other options presented are very likely to drown you in details.

It really depends on what sort of access you have to the database and the application code, and how well it is coded. If it follows good practice and specifies the columns in selects and inserts and the like, then you can add a timestamp column. If its badly coded adding this column might break things.

If you can't change the database for any of a number of reasons, then profiler or a 3rd party tool. You'll be stuck doing some really hard-out analysis here, as it will list tables that are modified - by their absence you'll have to figure out the tables not listed, and then take it with a grain of salt as your sample time might be too small to have picked up the change.

HTH
  David
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now