Solved

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

Posted on 2013-05-09
6
245 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
replication - alerts? 4 31
Convert SP in a format for debugging 7 30
Using CTE to insert records into a table 2 29
Extract string portion 2 14
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

920 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

15 Experts available now in Live!

Get 1:1 Help Now