Link to home
Create AccountLog in
Avatar of Easwaran Paramasivam
Easwaran ParamasivamFlag for India

asked on

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

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.
Avatar of BurundiLapp
BurundiLapp
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Easwaran Paramasivam

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of BurundiLapp
BurundiLapp
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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?
No it should parse all the stored procedures in the database that the procedure is created in.
Thanks.