[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

SQL Statement - for dropping archived tables

HI

We have a simple process of archving tables before updates are done here by dev.
We historically had dba's all do it different ways - so now we have a bunch of tables in databases that have different backup names that need to be deleted - dropped from the database.

name types include

_tablename_12052011
_tablename_call_100200_120511
tablename_bak_12052011
etc
Can anyone write me a script that can pick up the tables and print them as results
And then provide a drop statement using print too perhaps that can be executed separately after checking the results found ...

So print tables to be dropped
generate drop statement of table names ready to be executed once checked

The script can have the table names as variables that we can define before running
as i mentioned each database may have slightly different backup names for the archived tables.

Id like the sql ready to work please...
Needs to work for 2005 and 2008 and if possible 2k but 2k not important as much so not a worry if not possible.
thanks

Mo
0
mooriginal
Asked:
mooriginal
  • 5
  • 3
1 Solution
 
Alpesh PatelAssistant ConsultantCommented:
Select 'Drop Table '+ name from sys.objects where type = 'u' and name like '%Order%'
0
 
anillucky31Commented:
It looks like last few character of backup tables are having integer and having _ (underscore). I am assuming that it has minimum 6 digit in the end. so i can retirve those tables with this query

SELECT * FROM INFORMATION_SCHEMA.tables WHERE ISNUMERIC(RIGHT(table_name, 6)) = 1 AND PATINDEX('_', table_name) > 0
0
 
anillucky31Commented:
You can use this query to delete the tables

DECLARE @TableName NVARCHAR(200)

DECLARE @SQLQuery NVARCHAR(500)

DECLARE DROPcur FOR SELECT table_Name FROM INFORMATION_SCHEMA.tables WHERE ISNUMERIC(RIGHT(table_name, 6)) = 1 AND PATINDEX('_', table_name) > 0
AND Table_Type = 'BASE TABLE'

OPEN DROPcur

FETCH NEXT FROM DROPcur INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLQuery = 'drop table ' + @TableName

PRINT @SQLQuery

/*if you want to delete table uncomment the code below*/
--EXECUTE(@SQLQuery)

FETCH NEXT FROM DROPcur INTO @TableName
END

CLOSE DROPcur
DEALLOCATE DROPcur
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mooriginalAuthor Commented:
getting error on

Incorrect syntax near the keyword 'FOR'.
0
 
anillucky31Commented:
see attached code
DECLARE @TableName NVARCHAR(200)

DECLARE @SQLQuery NVARCHAR(500)

DECLARE DROPcur cursor FOR SELECT table_Name FROM INFORMATION_SCHEMA.tables WHERE ISNUMERIC(RIGHT(table_name, 6)) = 1 AND PATINDEX('_', table_name) > 0 
AND Table_Type = 'BASE TABLE'

OPEN DROPcur

FETCH NEXT FROM DROPcur INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLQuery = 'drop table ' + @TableName

PRINT @SQLQuery

/*if you want to delete table uncomment the code below*/
--EXECUTE(@SQLQuery)

FETCH NEXT FROM DROPcur INTO @TableName
END

CLOSE DROPcur
DEALLOCATE DROPcur

Open in new window

0
 
mooriginalAuthor Commented:
Just some comments - the tables all have '_' in them some shape or form either at beginning or in suffix of the table

thanks for updated code
ive run it and its not done anything - it executed successfully i did not change it any form before running from above post.

it didnt produce any print
0
 
anillucky31Commented:
can you tell me pattern of your archived tables? i mean some sample archived tables.

you have to run this script on the same database where u are having archived tables
0
 
anillucky31Commented:
try this code
DECLARE @TableName NVARCHAR(200)

DECLARE @SQLQuery NVARCHAR(500)

DECLARE DROPcur cursor FOR SELECT table_Name FROM INFORMATION_SCHEMA.tables WHERE ISNUMERIC(RIGHT(table_name, 6)) = 1 AND charindex('_', table_name) > 0 
AND Table_Type = 'BASE TABLE'

OPEN DROPcur

FETCH NEXT FROM DROPcur INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLQuery = 'drop table ' + @TableName

PRINT @SQLQuery

/*if you want to delete table uncomment the code below*/
--EXECUTE(@SQLQuery)

FETCH NEXT FROM DROPcur INTO @TableName
END

CLOSE DROPcur
DEALLOCATE DROPcur

Open in new window

0
 
mooriginalAuthor Commented:
nice
you got the points

thanks for the effort.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now