Idenitfy list of tables that not undergo any insert or delete operation?

Posted on 2013-05-15
Last Modified: 2013-05-22
Is there any easy regex search way to identify whether no INSERT or DELETE happens in particular table?

The insert statement could be INSERT INTO tablename or INSERT tablename. The space could be more between the words. The same could be applied for DELETE.

How to search tables in a database that are not undergo insert or delete operation in all SPs, UDFs,..?

Please do assist.
Question by:Easwaran Paramasivam
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
  • 3
  • 3

Expert Comment

ID: 39167378
Depends on how many Stored Procedures or Functions you have.

It depends what your outcomes are but I would monitor the tables using a trigger to see which are ones have INSERT or DELETE run against them and then store that information into a seperate table.  Downside is it would take time to accumulate the data.

I would first create a table to store the tablename and last modification date.
CREATE TABLE [dbo].[Table_Monitor](
	[TableName] [nchar](80) NULL,
	[LastActiondate] [datetime] NULL


Open in new window

And then place a trigger on all the other tables I wanted to monitor:

CREATE  TRIGGER [dbo].[DBTableMonitor1] 
ON [dbo].[Table_Monitor]
	DECLARE	@Date		DateTime,
			@Table 		NCHAR(80)

	SET @Table = 'Put Table Name Here'
	SET @Date = (SELECT GetDate())

	IF NOT Exists (SELECT * FROM Table_Monitor WHERE TableName = @Table)
		INSERT INTO Table_Monitor (TableName, LastActionDate) VALUES (@Table,@Date)
		UPDATE Table_Monitor SET LastActionDate = @Date WHERE TableName = @Table

Open in new window

The Trigger name would need incrementing each time as trigger names have to be unique and the table name would need inputting to reference the table the trigger is on.

This would build up the Table_monitor table with all the info about the tables with that trigger on them on your system that have an INSERT or a DELETE run on them.
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39167487
If I go for trigger operation, then I need to perform each and every transaction to insert or delete records in the tables. Moreover the number of tables are more.

Hence please do suggest solution to achieve with existing tables and database without creating trigger.

Accepted Solution

BurundiLapp earned 500 total points
ID: 39167527
ok, if you add this Sp to your database you can then reference it to search within stored procedures.  It may require multiple searches to determine exactly what you are after though.

@StringToSearch varchar(100) 
   SET @StringToSearch = '%' +@StringToSearch + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @stringtosearch

Open in new window

Then run it like this:

exec FindTextInSP 'insert%into'

Open in new window

You can use % to indicate multiple characters or ? to indicate a single character.

The code came from here:
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39167556
That should be run for each and every table. Isn't it?

I've numerous tables in my database. Is there any other easy way to do it faster?

Expert Comment

ID: 39167568
No it should parse all the stored procedures in the database that the procedure is created in.
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39187342

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.

Question has a verified solution.

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

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

763 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