Solved

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

Posted on 2013-05-09
6
248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Master DB with Masterkey 1 40
While in ##Table - Help 4 22
PROPERCASE SCRIPT IN SQL 3 24
Cannot connect to SQL express 2008 R2 installed on Server 2016 5 21
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 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