• the location to download the software;
• the ISUG Tech Journal article discussing background and architecture of these SSPs;
• the FAQ for the SSPs.

• sp_rv_dboption : just like the well-known sp_dboption , except that it's much better.

• sp_rv_findobject : the Swiss Army knife for Sybase DBAs.

• sp_rv_update_statistics : update statistics made easier than ever !

• sp_rv_helplogin : who's who in the server.

• sp_rv_exec : the most versatile stored procedure ever written ? You decide...

• sp_rv_dropobject : the most dangerous stored procedure ever written ? You decide...

• sp_rv_exec_include_db : restrict the databases to be accessed by the above commands.
• sp_rv_exec_exclude_db : exclude databases from being accessed by the above commands.
• sp_rv_exec_list_db : show list of databases which will be accessed by above commands.

• the SQL source code is not "hidden" anymore
• sp_rv_findobject: EXECARG also allows the ID format option (=object ID)
• sp_rv_findobject: OUTPUT and DISPLAY can be specified together
• there are two versions now: one for 11.9 or earlier, and one for 12.0 or later
• when running 12.0, sp_rv_update_statistics uses "execute immediate" instead of CIS for the "execute" option
• when running 12.0, sp_rv_findobject will execute the string generated by EXECARG= through "execute immediate" when "EXEC=IMMEDIATE" is specified as well.

sp_rv_dboption [dbname, optionname, {true | false}]
        

• When setting a database to "single user" from within the database itself, the database option will not be set if there's another session accessing the database (an informational message will be displayed mentioning this situation).

sp_rv_findobject [option1 [, option2 [, (etc.) ]]]
        

• "NAME=/NAME! =" : an object name or search pattern.
• "TYPE=/TYPE! =" : an object type (no search patterns allowed)
• "OWNER=/OWNER! =" : an object owner (= a database user) or search pattern.
• "COLNAME=/COLNAME! =" : a column name or search pattern.
• "COLTYPE=/COLTYPE! =" : a column data type or search pattern.
• "DB=/DB! =" : a database name or search pattern.
• "CREATED< =" : a date/time the object should be created before.
• "CREATED> =" : a date/time the object should be created after.
• "EXEC =" : a stored procedure to be executed for every object found.
• "EXECARG =" : specify parameters to pass to the 'EXEC=" procedure.
• "OUTPUT =" : a table name (in 'tempdb') to hold the results.
• "DISPLAY =" : a format string for displaying the results.

• EXEC= and DISPLAY=
• EXEC= and OUTPUT=
  

• NAME=/NAME!= : specify a object name or a search pattern.
  Example:

"NAME=abc%"
-- finds objects having a name starting with "abc";

"NAME!=[^xy]%"
-- finds objects having a name not starting with 
-- "x" or "y";

"NAME=_"
-- finds objects having a 1-character name.                
            

• TYPE=/TYPE!= : specify a object type (search patterns are not possible).
  Example:

"TYPE=U"
-- finds only user tables

"TYPE!=U"
-- finds anything except user tables

Possible object types are the normal values in sysobjects.type, i.e. U, S, P, TR, R, D, V, and XP. Also possible are "UDD" (User-Defined Datatype) and PRX (Proxy Table).

• OWNER=/OWNER!= : specify an object owner name or a search pattern.
  Example:

"OWNER=jsmith"
-- finds objects owner by database user "jsmith";

"OWNER!=dbo"
-- finds objects not owned by "dbo";

"OWNER=sales_dept%"
-- finds objects owned by any user in the 
-- Sales Dept (you get the idea...)
            

• COLNAME=/COLNAME!= : specify a column datatype or search pattern.
  Example:

"COLNAME=__"
-- finds 2-character column names;

"COLNAME=%customer%"
-- finds column names containing "customer";
            

• COLTYPE=/COLTYPE!= : specify a column datatype or search pattern.
  Example:

"COLTYPE=%identity%"
-- finds all identity columns.

"COLTYPE=%<%"
-- finds all columns with a user-defined 
-- datatype (a user-defined datatype is displayed 
-- between <> brackets)

"COLTYPE=%my_type%"
-- finds all columns of datatype 'mytype'

"COLTYPE=%(20)%"
-- finds all (var)char(20) and (var)binary(20) columns

"COLTYPE!=%smallint%"
-- all columns except 'smallint' datatypes
            

• DB=/DB!= : specify a database name or search pattern.
  Example:

"DB=mydb"
searches only database "mydb"

"DB=mydb%"
-- searches databases "mydb1", "mydb2", etc.

"DB!=mydb"
-- will not search database "mydb"
            

• CREATED<= : specify a date/time value before which the object should have been created.
  Example:

"CREATED<=22-Feb-1999 22:00:00"
-- finds objects created before this time
            

• CREATED>= : specify a date/time value after which the object should have been created.
  Example:

"CREATED>=05-Jan-1999 13:15:00"
-- finds objects created after this time
            

• EXEC= : specify a "plug-in" stored procedure to be executed for every object found.
  Example:

"EXEC=sp_helpindex"
-- runs sp_helpindex on objects found
            

Note that you can write your own plug-in stored procedures to perform specific actions, and invoke these through "EXEC=".

In version 2.1 of these "New generation Stored Procedures" (released October 2000), and when running ASE 12.0 or later, you can also specify "EXEC=IMMEDIATE". This will execute the string generates through the EXECARG= parameter through "execute immediate". This allows you to generate more flexible SQL statements dynamically.
Example:


"EXEC=IMMEDIATE", 
"EXECARG=select '# Tables in database DB=' +
str(count(*),5) from sysobjects",
"NAME=sysobjects"
-- print the total number of tables in 
-- each database in the server
            

• EXECARG= : specify what information should be passed to the procedure specified in "EXEC=", which will be passed one parameter. Possible specifiers are (note that these are case-sensitive) :
  • DB - database name
  • OW - object owner name
  • NM - object name
  • CL - column name
  • ID - object ID
  Any other characters in the "EXECARG=" string will be copied to the parameter value directly (such as the "." in the below example).
  Example:

"EXEC=sp_helpindex", "EXECARG=OW.NM"
-- this runs sp_helpindex on objects found, passing
-- "owner.objectname" as a parameter.
            

• OUTPUT= : specify a table to hold the final search results. This table will be created in "tempdb".
  Example:

"OUTPUT=search_results"
-- puts final results in the table
-- "tempdb..search_results"
            

When the specified table already exists, a different, but unique, table name will be generated.

• DISPLAY= : specify which attributes are to be included in the final output.
  Example:

"DISPLAY=NM, SZ"
-- displays object name and size/rowcount
            

Possible specifiers are (note that these are case-sensitive) :
• DB - database name
• OW - object owner name
• NM - object name
• CL - column name
• TP - object type
• CL - column name
• CT - column datatype
• CR - object creation date
• SZ - object size & rowcount (for tables only)
• ID - object ID
Any other characters in the "DISPLAY=" string will be copied to the parameter directly (such as the ", " in the above example). This can be used for formatting the results, for example, try the following:


"DISPLAY= Object OW.NM (has object_id: ID)"
            

• You must have "sa_role" to run this procedure.

• "sp_rv_findobject" may cause significant locking activity on the system tables "systypes", "syscolumns" and "sysobjects", depending on the search options specified. For example, specifying only the "COLTYPE=" option in a heavily used database with many objects may result in performance degradation.

• When specifying the "EXEC=" option, the resulting system load depends on the number of objects found and the specific actions performed by the plug-in stored procedure specified in the "EXEC=" option.

• Be careful when using the "EXEC=" option to perform some action that affects the data or the data model. sp_rv_findobject will happily do anything you tell it, even if this has disastrous consequences (there's an example here of what you can, but should not, do!). Therefore, be very careful before running with the "EXEC=" option; first run without it, and double-check the search results.

• For "CREATED>=" and "CREATED<=", the normal ASE conversion rules for datetime values apply: when not specifying a time, midnight is assumed; i.e. "15-Apr-1999" means "15-Apr-1999 00:00:00".

• When specifying an underscore ("_") in a search pattern, this will match any single character, following the normal ASE pattern matching rules. In order to match an underscore character, enclose it in square brackets. The following finds all objects named "a_c", but not "abc" :

exec sp_rv_findobject "name=a[_]b"
            

• When using the "EXEC=" option, the plug-in stored procedure may, under certain circumstances, be invoked more times than expected. For example, suppose you want to run "sp_spaceused" for all tables containing a column that starts with an "A" using the following command:

exec sp_rv_findobject "colname=A%",
     "exec=sp_spaceused"
            

The result will be that when there's a table with two columns that both start with an "A", "sp_spaceused" will be invoked twice for that table. Currently, this cannot be avoided.

• The "DB=" option may interfere with settings made earlier in the session through " sp_rv_exec_include_db " and " sp_rv_exec_exclude_db ". The databases accessed will be the logical "AND" of all settings involved.

• When the search results are displayed on the screen, the results are truncated at 80 characters to avoid long lines messing up the screen. Should you have output which longer than this, you can also use the "OUTPUT=" option to put the results in a table, and generate your output from this table with a custom SELECT statement.

sp_rv_update_statistics
            {"generate" | "execute"}
            [, ""|"all"|"index"|"partition"
            [, database_name | pattern ]]
        

exec sp_rv_update_statistics "execute"
        

exec sp_rv_update_statistics "generate"
        

exec sp_rv_update_statistics "execute", "all"
        

exec sp_rv_update_statistics "execute", "", "sales%"
        

exec sp_rv_findobject
     "name=A%", "owner=jsmith",
     "exec=sp_rv_update_statistics",
     "execarg=execute,all"
        

• You must have "sa_role" to run this procedure.

• "update statistics" code is only executed or generated for normal user tables: #temporary tables and proxy tables are skipped.
  Updating statistics for proxy tables should be done seperately by the DBA (reason: there are certain CIS-related traceflags that the DBA might want to use to avoid that these commands will take a long time to complete).

• When specifying a database name or search pattern, this may interfere with settings made earlier in the session through " sp_rv_exec_include_db " and " sp_rv_exec_exclude_db ". The databases accessed will be the logical "AND" of all settings involved.

• The second parameter is not validated; i.e. "update all statistics" in ASE 11.5 will lead to an error. In fact, you can specify any string as the second parameter, for example "zzz" ; this will result in the command "update zzz statistics" which will generate an error when executed.

• When running "sp_rv_cis_setup_1" and "sp_rv_cis_setup_2" to configure the server for using CIS, there is a security issue to be aware of. These procedures add a row to "master.dbo.sysremotelogins" to allow CIS to log into the server as login "sa" (this assumes that the "sa" login will have "sa_role"). You should verify this will not create any security problems in your system, and modify the remote login definitions if required. Note that this may result in
  Note that this row in "master.dbo.sysremotelogins" is not displayed when you run "sp_helpremotelogin", because this procedure can't handle remote logins for the local server. Therefore, you should issue direct SELECT statements on "master.dbo.sysremotelogins" to inspect the data in this table.

• When running "sp_rv_cis_setup_1", and your local servername isn't defined, you must specify your local servername to "sp_rv_cis_setup_1" as a parameter; this name will then be set as your local servername which will become active after you restart your server.
  Furthermore, "sp_rv_cis_setup_2" will add a row to "master.dbo.sysservers" which maps a server named YOURSERVER_MYSELF to YOURSERVER (where YOURSERVER is your servername). This is a trick to allow CIS to log in to the same server via a back-door.
  Lastly, some CIS-related configuration options are modified.

sp_rv_helplogin [login_name [, database_name]]
        

exec sp_rv_helplogin "jsmith"
        

• You must have "sa_role" to run this procedure.

• User-defined roles are not taken into consideration; for example, a user-defined role containing "sa_role" would cause a login to be "dbo" in all databases; however, the login's corresponding database users will be displayed as if the role were not there.

• When specifying a database name, this may interfere with settings made earlier in the session through " sp_rv_exec_include_db " and " sp_rv_exec_exclude_db ". The databases accessed will be the logical "AND" of all settings involved.

sp_rv_exec proc_name [, param1 [, param2 ...]]
        

exec sp_rv_exec "sp_adduser", "newlogin"
-- add a login as a user to all databases in the server

exec sp_rv_exec "sp_dropuser", "newlogin"
-- ... and drop the user again

exec sp_rv_exec "sp_addtype", "mytype",
                "varchar(25)", "not null"
-- add a user-defined datatype to all databases in the server
        

• the "plug-in" SSP you want to execute in every database should exist in the "sybsystemprocs" database and have a name starting with "sp_";
  
• you can specify a maximum of 15 parameters for the "plug-in" SSP.

• You must have "sa_role" to run this procedure.

• "sp_rv_exec" takes only character-type parameters. This means that you should specify the parameters to "sp_rv_exec" as strings, even when the plug-in stored proc actually has an integer parameter ("sp_rv_exec" attempts to perform a conversion). In practice, this means you best quote all parameters to "sp_rv_exec".

• Parameters for the "plug-in" SSP should be specified "by position" when calling "sp_rv_exec"; it's not possible to specify these "by name" (i.e. @param1="value"). The reason is that "sp_rv_exec" just passes the parameters you specified to the "plug-in" SSP in the same order.

• The plug-in procedure is called with explicit parameter values for all parameters, even when you didn't specify all parameters to "sp_rv_exec". This means that default values for the plug-in procedure's parameters will never be assigned.
  Parameters that were not specified to "sp_rv_exec" will be replaced with an explicit NULL value. Therefore, this issue will only be a problem when default parameter values other than NULL are used.

• When writing your own plug-in system stored procedures to perform some specific task through "sp_rv_exec", best use only (var)character-type parameters, and avoid "binary" and "varbinary". If you mix different datatypes in your parameters, "sp_rv_exec" may not be able to convert the supplied parameter values to the proper datatypes. In this case, an error message will be displayed.

sp_rv_dropobject [owner.]objectname
        

/* WARNING: do not execute this statement ! */
exec sp_rv_findobject "exec=sp_rv_dropobject" 
        

• You must have "sa_role" to run this procedure.

• When there is both an object (table, view, procedure, etc.) and a user-defined datatype having identical names (and both of the same owner), sp_rv_dropobject will always drop the object (table, view, etc.), not the datatype. In order to avoid this problem, ensure that user_defined datatypes have names that are different from any object names. You can also (temporarily) rename a user-defined datatype through "sp_rename" so that this problem won't occur.

sp_rv_exec_include_db [mydb1 [, mydb2 ...]]
        

exec sp_rv_exec_include_db mydb1, mydb2, mydb3
        

• These settings apply to "sp_rv_exec", as well as to all procedures built on top of "sp_rv_exec", such as "sp_rv_findobject".

• Settings made by "sp_rv_exec_include_db" and "sp_rv_exec_exclude_db" may interfere in an unexpected manner. There precedence rules are as follows:

1. if no databases have been specified through "sp_rv_exec_include_db", access all databases;
2. if "sp_rv_exec_include_db" was used, access only those databases specified;
3. do not access any databases specified through "sp_rv_exec_exclude_db".

exec sp_rv_exec_include_db mydb1
exec sp_rv_exec_exclude_db mydb1
            

sp_rv_exec_exclude_db [mydb1 [, mydb2 ...]]
        

exec sp_rv_exec_exclude_db mydb1, mydb2, mydb3
        

• Same as for "sp_rv_exec_include_db"

sp_rv_exec_list_db [ "reset" ]
        

exec sp_rv_exec_list_db
        

exec sp_rv_exec_list_db "reset"
        

• None.