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
There are many, many ways you could handle adding a list of logins/users to Sybase ASE, all of which include a mix of (Unix/Linux) shell scripting and T-SQL coding.
One simple example:
set passs=`cat /cis1/prod/dba/input/. ----`
cat addthese.txt | while read newlogin
do
isql -Usa -SPDSQTS1 <<EO_SQL > /tmp/error.out 2>&1
$passs
use master
go
if suser_id("${newlogin}") is NULL
exec sp_addlogin ${newlogin},Sybase123
go
----------------------
-- repeat following block for each db of interest (eg, qts_db, engcomdb)
use <db>
go
if user_id("${newlogin}") is NULL
exec sp_adduser ${newlogin}, ${newlogin}, eng
go
---------------------
-- rest of your coding
go
EO_SQL
... check for errors in /tmp/error.out ...
done
You could place all of the above into a shell script, make sure addthese.txt is correct/up-to-date, and then run the shell script.
Variations on the above could include:
putting the T-SQL code in a stored proc and then calling the stored proc for each new login
placing the above T-SQL code in a template.sql file with NEWLOGIN
used as place holder, a shell script would then run sed 's/NEWLOGIN/${newlogin}/g' template.sql > runme.sql
followed by isql -i runme.sql > error.out 2>&1
There are many variations based on which shell you're using and your coding style/preferences.
Best Answer
The ASE dataserver is configured with a single sort order; see selecting default sort order.
For scenarios where the user needs to use a sort order different from the dataserver's default sort order there is: