[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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

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
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
3 Solutions
 
Marten RuneCommented:
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
 
David ToddSenior DBACommented:
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
 
Easwaran ParamasivamAuthor Commented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Anthony PerkinsCommented:
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
 
Marten RuneCommented:
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
 
David ToddSenior DBACommented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now