Ho Can I Delete Old Data From All Tables In Sql Database

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hi,

I have 10,000 Tables,Views,group of objects and Primery key relation
How can i delete data from database means i want to delete one month data from all tables

Example i have Jan to Dec Month data in database
i want to delete Jan & Feb Months data without breaking table structure

Please suggest

Posted 3-Dec-15 22:54pm

Member 12185626

Updated 4-Dec-15 1:07am

v2

Add a Solution

Comments

John C Rayan 4-Dec-15 5:32am

Explain a bit more clearer by improving the question.

phil.o 4-Dec-15 5:39am

You may have to describe the relevant elements of your database schema if you want a meaningful answer. Please click the "Improve question" button.

_Asif_ 4-Dec-15 5:51am

You need to provide schema information, specially how you going to filter data based on date. Do you have created_on type of column n all tables?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mr. Mahesh Patel · Answer 1 · 2015-12-04T00:03:00

SQL

USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TblEmployee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EmpName] [varchar](20) NULL,
	[JoinDate] [datetime] NULL,
 CONSTRAINT [PK_TblEmployee] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

INSERT INTO [dbo].[TblEmployee]([EmpName],[JoinDate])VALUES('Mahesh Patel','2015-01-01')
INSERT INTO [dbo].[TblEmployee]([EmpName],[JoinDate])VALUES('Rajesh Koriya','2014-01-01')
INSERT INTO [dbo].[TblEmployee]([EmpName],[JoinDate])VALUES('Dhaval Patel','2014-01-01')
INSERT INTO [dbo].[TblEmployee]([EmpName],[JoinDate])VALUES('Krutarth Shah','2014-01-01')

Delete from table Dynamically

SQL

USE [MyDatabase]
GO
DECLARE @Query NVARCHAR(MAX)
DECLARE MyCursor CURSOR LOCAL FOR
SELECT 'DELETE FROM ' + NAME +' WHERE JoinDate = ' +CHAR(39)+ '2014-01-01' + CHAR(39) as Query from Sys.Objects where type = 'U'
OPEN MyCursor
	FETCH NEXT FROM MyCursor INTO @Query
	WHILE  @@Fetch_Status=0            
	BEGIN
		PRINT(@Query)
		EXEC(@Query)
		FETCH NEXT FROM MyCursor INTO @Query
	END
CLOSE MyCursor
DEALLOCATE MyCursor

JasonClark03 · Answer 2 · 2015-12-06T19:02:00

Solution 3

Try to run this:

Select  'delete from '
     ||  object_name
     ||' where created_datetime <='
     ||' TRUNC(SYSDATE,'||''''||'YEAR'||''''||');' 
     delete_old_rows
from user_objects
where object_type = 'TABLE';

May this helpful for you.

Posted 6-Dec-15 19:02pm

JasonClark03

Updated 7-Dec-15 19:39pm

v2