我可以从Sql数据库中的所有表中删除旧数据 [英] Ho Can I Delete Old Data From All Tables In Sql Database

问题描述

我有10,000个表，视图，一组对象和Primery密钥关系

如何从数据库中删除数据意味着我要从所有表中删除一个月的数据



示例我在数据库中有1月到12月的数据

i想要删除Jan& 2月份数据没有破坏表结构



请建议

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

推荐答案

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

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

尝试运行：

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.

这篇关于我可以从Sql数据库中的所有表中删除旧数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！