Solved

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

Posted on 2013-05-09
6
247 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

860 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