Ever wanted to execute a SQL statement based on using column values in a table, for selected rows in the table?
If your response is 'Huh?', let's put it differently: if you're a DBA, suppose you want to lock all logins with a particular default database, but not touch the other logins (or how do you change their default database? or both?) How do you do this easily if you have thousands of logins?
The classic solution is to write a little loop that runs through the table (in the above example: syslogins) and executes what you want to have done (in the example: lock the login if it has the specified default database). In other words, you write custom code.
Writing custom code works, but I got really tired of it. A few years ago I create a stored procedure that does this for you. While working on a problem recently, I dug up this procedure and realised it might be useful to make it available to the Sybase community.
So here it is: the stored procedure sp_exec_table -- a real workhorse and timesaver that may especially be useful for DBAs, for use in tools, and probably for a few other things I haven't thought of.
Apart from its practical usefulness, sp_exec_table combines some interesting applications of SQL features, like execute-immediate. Interested readers are invited to check out the source code.
To install the stored procedure, follow these steps:
- Execute this script in ASE 18.104.22.168 or later with isql, using a login having sa_role.
In its simplest form, you need to specify three parameters to sp_exec_table:
- The name of a table (or view) on the basis of which you'll be executing your SQL commands
- An expression based on one or more columns from the table above
By default, the SQL command is executed for every row in the table. Optionally a where-clause and order-by-clause can be specified to control execution. Some additional options exist; see the online syntax help for details.
- The SQL command to be executed. For every qualifying row in the table above, the statement is executed; prior to execution the placeholder '###' (without quotes)
will be replaced by the evaluated column expression above. Also, '##N' (case-insensitive) will be replaced by the iteration number.
-- lock all logins with default database 'mydb'
sp_exec_table 'master..syslogins', name, 'sp_locklogin ###, "lock" ',
'where dbname = "mydb" '
-- drop all user tables in the current database
sp_exec_table sysobjects, 'user_name(uid) + "." + name', 'drop table ###',
-- bind all user tables named 't%' to a named cache
sp_exec_table sysobjects, 'db_name() + "," + name',
'sp_bindcache "my_cache", ###',
'where type ="U" and name like "t%" '
-- display usage and syntax info
Apart from the regular disclaimers, please note the following:
- sp_exec_table relies heavily on the combination of execute-immediate and 16Kb run-time string expressions, as well as str_replace(); therefore it can only be used in ASE 22.214.171.124 or later.
- See the header of the downloaded file for additional usage notes. Read it -- it contains some things you should be aware of.
- Version 1.1 - Apr-2007 - Various enhancements
- Version 1.0 - Sep-2004 - First version