Solved

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

Posted on 2013-05-15
6
219 Views
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.
0
Comment
Question by:Easwaran Paramasivam
  • 3
  • 3
6 Comments
 
LVL 6

Expert Comment

by:BurundiLapp
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
) ON [PRIMARY]

GO

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]
FOR INSERT, DELETE
AS
BEGIN
	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)
	ELSE
		UPDATE Table_Monitor SET LastActionDate = @Date WHERE TableName = @Table
	
END
GO

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.
0
 
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.
0
 
LVL 6

Accepted Solution

by:
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.

CREATE PROCEDURE FindTextInSP
@StringToSearch varchar(100) 
AS 
   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
   ORDER BY SO.Name
GO

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: http://stackoverflow.com/questions/2283826/how-can-i-programmatically-determine-if-a-stored-procedure-selects-from-another
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 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?
0
 
LVL 6

Expert Comment

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

Author Closing Comment

by:Easwaran Paramasivam
ID: 39187342
Thanks.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
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…

785 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