Drop Tables using Sql Script / VB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Drop Tables using Sql Script / VB

Author

Topic

olily
Starting Member

37 Posts

Posted - 2002-05-12 : 19:50:58

I have about 40 tables per day in tempdb which I would like to housekeep by writing a sql script or a vb program to automate the process. The problem is I do not know the table name. I can identify the name by Type=User and my table name format is tmp_mmddyyhhmmss. I'm using Sql Server 7.0 and VB6.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-13 : 00:34:11

no problems olily-

check out Books-On-Line (BOL) under INFORMATION_SCHEMA.

You might want something like
select *
from INFORMATION_SCHEMA.TABLES
where table_name like 'tmp%'

etc

Then just build your SQL Strings -

eg to delete all your temp tables more than 1 day old you could do something like:

SQL = "select table_name from information_schema.tables "
SQL = SQL & "where datediff(dd,CONVERT(datetime, SUBSTRING(@TABLE_NAME,5,2)+'-'+SUBSTRING(@TABLE_NAME,7,2)+'-'+SUBSTRING(@TABLE_NAME,9,2), 10),getdate()) > 1"

rst.Open SQL, Cnxn

Do while Not rst.EOF
dropTableSQL = "drop table " & rst.Fields("TABLE_NAME")
rst1.Open dropTableSQL, Cnxn
...etc etc....
Loop

Of course you'll need to tidy that up abit - I'd probably make a view which returns the tables you want to delete, to make your string concatenation easier

etc etc

HTH

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

olily
Starting Member

37 Posts

Posted - 2002-05-14 : 00:48:50

Thanks! This really make my work easier!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources