SQL SERVER – HOW TO DELETE ALL TABLES DATA EXCEPT TOP N RECORDS IN EACH TABLE?

I always likes logical work than regular boring work. So when I am browsing on Google, I found some interesting question like how to delete all tables’ data in SQL Server except top N records in each table?

Here is the script for delete all tables data SQL Server DB except top N records in each table

 

Note:

Considering all tables do not have any inter dependency on each other

IF OBJECT_ID('TEMPDB..#TABLE') IS NOT NULL

    DROP TABLE #TABLE

DECLARE @TOPN INT=1000    ---HOW MANY RECORDS YOU WANT TO KEEP IN EACH TABLE

CREATE TABLE #TABLE

(

ID INT IDENTITY(1,1),

TABLEQUERY VARCHAR(MAX)

)

INSERT INTO #TABLE

(

TABLEQUERY

)

SELECT

' DECLARE @TOP INT;

SELECT @TOP = (COUNT(1) -' + CAST(@TOPN AS VARCHAR(10)) +' ) FROM ' + B.NAME + '.'+ LTRIM(RTRIM(A.NAME)) + ';'

+ ' IF @TOP > ' + CAST(@TOPN AS VARCHAR(10)) +'

DELETE TOP (@TOP)  FROM ' + B.NAME + '.'+ LTRIM(RTRIM(A.NAME)) + '; PRINT ' + '''' + 'DELETING DATA FROM' + B.NAME + '.'+ LTRIM(RTRIM(A.NAME)) + '''' +';'

FROM SYS.TABLES A INNER JOIN SYS.SCHEMAS B ON A.SCHEMA_ID = B.SCHEMA_ID

DECLARE @ID INT=1

DECLARE @MAX INT=(SELECT COUNT(1) FROM #TABLE)

DECLARE @QUERY VARCHAR(MAX)

WHILE @ID<@MAX

BEGIN

SET @QUERY=(SELECT TABLEQUERY FROM #TABLE WHERE ID=@ID)

EXEC (@QUERY)

SET @ID=@ID+1

END

• email
• facebook
• linkedin
• twitter
• reddit
• google+

Kiran Reddy A

Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL

Get Free Email Updates to your Inbox!

• Rss
• Twitter
• FaceBook
• Google +