Sybase ASE 12 question

Question

I support a database that has many tables that hold very similar data. Over time, much of this data isn't needed, and the table names are dynamic as an application is creating these tables, and the current date is used within the table name.

I am attempting to write a batch file that uses isql to remove rows that are no longer required to store in all of the tables. They all start off with the same set of characters, followed by _creation date.

Is there any way to write a script to find those names, or use a wildcard such as:

DELETE FROM Table_* WHERE Type = 1

From my testing, the above SQL command does not work. Is there a way around this without having to specify the exact table name?

what is the table naming convention e.g _creation date is how - Table_20140324 ? You can use dynamic SQL to do that. Let me know and I can help you out. — Kin Shah
– Kin Shah, Commented Mar 24, 2014 at 17:09
Example would be table_03_24_14 if a table were created today — Drew
– Drew, Commented Mar 24, 2014 at 18:04

Michael Gardner · Accepted Answer · 2014-03-25 14:42:20Z

Sybase ASE will not allow you to directly delete rows from multiple tables using a wild card, but it's pretty simple to create a script to find the table names, and loop through them.

You can find the tables names by querying sysobjects within the database.

To find all the tables with the prefix Table_ you would do:

select name from myDatabase..sysobjects where type = "U" and name like "Table\_%" escape "\"

In the above query "U" is the flag for User defined tables. Also because _ is a single character wild card in T-SQL, we have to escape it to find the literal _. T-SQL allows almost any character to act as the escape, but it must be defined after the like clause. Finally % is the multi-character wild card, the T-SQL equivalent to * in *nix and DOS.

It would also be possible to use the above query to build a sql script. We start by telling the server not to print out the number of rows affected by the query using set nocount. This allows us to create a script that will run error-free, without needing additional editing.

set nocount on \\supressess rows affected printout select "delete from " +name+ " where type = 1" + char(10) + "go" from myDatabase..sysobjects where type = "U" and name like "Table\_%" escape "\" go

If you execute this from isql and using the -b flag to supress headers, and -o flag to redirect output to a file, it will build a script that can then be executed directly.

isql -Uusername -Ppassword -Sservername -b -omyScriptToDeleteData.sql -iSQLToFindTables.sql

Stack Exchange Network

Sybase ASE 12 question

1 Answer 1

Hot Network Questions

Sybase ASE 12 question

1 Answer 1

Related

Hot Network Questions