print versionprint version  
Sybase ASE
11.0 11.5 11.9 12.0 12.5 15.x
Last updated: 06 December 2000
Manual Pages for "New Generation ASE System Stored Procedures"
 
Other links about the "New Generation ASE System Stored Procedures" :
  • the location to download the software;
  • the ISUG Tech Journal article discussing background and architecture of these SSPs;
  • the FAQ for the SSPs.


These are the "New Generation ASE SSPs". For detailed information how to use them, follow these links:
  • sp_rv_dboption : just like the well-known sp_dboption , except that it's much better.
  • sp_rv_exec : the most versatile stored procedure ever written ? You decide...
  • sp_rv_dropobject : the most dangerous stored procedure ever written ? You decide...

To execute these stored procedures, you need "sa_role" : these aren't the sort of procedures you'd give to just any user...


Changes in version 2.1 (26 October 2000):
  • 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

Syntax:
sp_rv_dboption [dbname, optionname, {true | false}]
        
Description:
This procedure does the same as the well-known "sp_dboption" (and takes exactly the same parameters), but it also does the checkpoint for you and it can be executed from anywhere (not just from the "master" database as "sp_dboption" requires). The big advantage: no need to issue the "use" command anymore when changing database options, so you can do it all in a single command. You can even set database options from a stored procedure now, by calling "sp_rv_dboption".

To obtain help information, execute this procedure with just a single "?" parameter.

Known problems & issues:
  • 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

Syntax:
sp_rv_findobject [option1 [, option2 [, (etc.) ]]]
        
Note that some changes were made in version 2.1 of this stored procedure (released October 2000).

Available options (click on link for details) :
  • "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.

Every option can be specified at most once. The following options are mutually exclusive:
  • EXEC= and DISPLAY=
  • EXEC= and OUTPUT=

Option details:
  • 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)"
                


Description:
The stored procedure sp_rv_findobject could also have been named sp_swiss_army_knife, because it's versatile and very powerful. Basically, "sp_rv_findobject" finds objects, in any database, anywhere in your server, according to search criteria you specify. Furthermore, it can also perform actions on the objects found, for which you can even write your own "plug-in" stored procedures to perform specific actions.
You can specify a series of search criteria which act as a search filter; they combine in a "logical AND". Each of the options described above can be specified at most once. Because some of the search options accept search patterns, you can build complex search filters.

To obtain help information, execute this procedure with just a single "?" parameter.

Here's some further examples of what you can do sp_rv_findobject .


Known problems & issues:

  • 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.
  • 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

Syntax:
sp_rv_update_statistics
            {"generate" | "execute"}
            [, ""|"all"|"index"|"partition"
            [, database_name | pattern ]]
        
Note that some changes were made in version 2.1 of this stored procedure (released October 2000).

Description:

This procedure will either perform "update statistics" and "sp_recompile" on all user tables in all databases (by default excluding "tempdb" and "model"), or generate the necessary T-SQL commands to perform these actions.

To actually perform "update statistics" and "sp_recompile" on all user tables in all databases, specify "execute" as the first parameter:
exec sp_rv_update_statistics "execute"
        
To use "execute", you need to run ASE version 11.5 or later, because CIS features (Component Integration Services) are used to run the "update statistics" commands in 11.5 and 11.9, and "execute immediate" is used in 12.0 and later".
In 11.5 and 11.9, some server configuration actions are required to properly set up CIS. When running the above command, the server configuration is examined first, and when additional setup is required, you will receive a message telling you to run the procedure "sp_rv_cis_setup_1", restart your server and then run "sp_rv_cis_setup_2". After this, you should be able to use the "execute" option without problems.

In case you're running ASE version 10.x or 11.0.x, you cannot use the "execute" option. However, you can generate the compete T-SQL script to perform these actions use the "generate" option:
exec sp_rv_update_statistics "generate"
        
In 11.5 or earlier, a plain-vanilla "update statistics" command will be generated. In case you want the additional options available since 11.5 (update "all" / "index" /"partition" statistics), you can specify this additional keyword in the second parameter. In 11.9 and later, "update index statistics" will be generated by default, unless an explicit option is specified as described below.
The following command will run "update all statistics" instead of "update statistics" :
exec sp_rv_update_statistics "execute", "all"
        
By default, "sp_rv_update_statistics" covers all databases in the server. You can limit the scope of action to one or more database by specifying the database name or a search pattern as the third parameter. The following command will run "update statistics" on all user tables in all databases whose names start with "sales":
exec sp_rv_update_statistics "execute", "", "sales%"
        
Lastly, when only some very specific tables should have their statistics updated, you can also use the following command form:
exec sp_rv_findobject
     "name=A%", "owner=jsmith",
     "exec=sp_rv_update_statistics",
     "execarg=execute,all"
        
This will run "sp_rv_update_statistics" only on the tables found by sp_rv_findobject. Note that you must specify the first and (optionally) second parameter for sp_rv_update_statistics through the "EXECARG=" option.

To obtain help information, execute this procedure with just a single "?" parameter.


Known problems & issues:
  • 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

Syntax:
sp_rv_helplogin [login_name [, database_name]]
        
Description:
Did you ever try to drop a login and you got the message that this isn't possible because the "User exists or is an alias or is a database owner (...)" ? You'll have a hard time finding out in which of your 35 databases this login still owns a datatype.
But now this becomes a trivial issue, as you would simply run "sp_rv_helplogin" :
exec sp_rv_helplogin "jsmith"
        
This will tell you which database user the login "jsmith" corresponds to in all databases in your server. Furthermore, it displays how many objects (including user-defined datatypes) the corresponding database user owns in each database.

When specifying a database name as the second parameter, the objects owned in that database will be also displayed (only when the user is not "dbo", "guest", or an alias).

To obtain help information, execute this procedure with just a single "?" parameter.


Known problems & issues:
  • 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

Syntax:
sp_rv_exec proc_name [, param1 [, param2 ...]]
        
Description:
"sp_rv_exec" is a versatile SSP that allows you to run any other SSP in all databases in just a single command: there's no need to switch databases with the "use" command anymore ! This is especially handy when you have many databases in your server.
In fact, " sp_rv_findobject ", " sp_rv_helplogin " and " sp_rv_update_statistics " are all built on top of "sp_rv_exec", but "sp_rv_exec" can also be used directly.
Examples:
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
        
To use "sp_rv_exec", stick to the following rules:
  • 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.
By default, "sp_rv_exec" covers all databases in the server. You can change this using the SSPs sp_rv_exec_include_db , sp_rv_exec_exclude_db and sp_rv_exec_list_db .

To obtain help information, execute "sp_rv_exec" with just a single "?" parameter.


Known problems & issues:
  • 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

Syntax:
sp_rv_dropobject [owner.]objectname
        
Description:
"sp_rv_dropobject" drops the object whose name is specified as a parameter. This works for tables, views, procedures, triggers, user-defined datatypes (when they're not in use), rules and defaults; other types of object (such as constraints or indexes), it can't currently handle.

BIG WARNING: Be very careful with this procedure, because it will not ask for confirmation ! Because you must be "dbo" to run this procedure, you have all permissions to drop objects !
If you find this procedure too dangerous, you should consider dropping the procedure from your server.


This procedure can be handy when you need to cleanup lots of objects. There's some examples here .
You can drop objects owned by any user. When the owner is not "dbo", you must specify the owner as part of the objectname. When you don't specify an owner, but just an objectname, the object is assumed to be owned by "dbo".


To illustrate the type of damage you can do when you're not careful, consider (but don't execute !), the following command:
/* WARNING: do not execute this statement ! */
exec sp_rv_findobject "exec=sp_rv_dropobject" 
        
This command will drop all tables, views, procedures etc. in your entire server (well, only those owned by "dbo", but that's usually bad enough...) with just a single command ! You have been warned !


Known problems & issues:
  • 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

Syntax:
sp_rv_exec_include_db [mydb1 [, mydb2 ...]]
        
Description:
By default, "sp_rv_exec", and all procedures built on top of it, search all databases in the server. You can restrict the databases to be accessed by these SSPs by specifying them as parameters to "sp_rv_exec_include_db" :
exec sp_rv_exec_include_db mydb1, mydb2, mydb3
        
From now on, only these three databases will be accessed by "sp_rv_exec".
A maximum of 10 databases can be specified for "sp_rv_exec_include_db". To specify more than 10 databases, just call "sp_rv_exec_include_db" again: repeated calls are cumulative.

Executing "sp_rv_exec_include_db" without any parameters will clear the list of databases to be searched and returns to the default of searching all databases. You can verify the current settings by running sp_rv_exec_list_db .

These settings are session-specific.


Known problems & issues:
  • 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".
For example, the following settings will cause "sp_rv_exec" not to access any databases at all:
exec sp_rv_exec_include_db mydb1
exec sp_rv_exec_exclude_db mydb1
            


sp_rv_exec_exclude_db

Syntax:
sp_rv_exec_exclude_db [mydb1 [, mydb2 ...]]
        
Description:
By default, "sp_rv_exec", and all procedures built on top of it, search all databases in the server. You can exclude databases to be accessed by these SSPs by specifying them as parameters to "sp_rv_exec_include_db" :
exec sp_rv_exec_exclude_db mydb1, mydb2, mydb3
        
From now on, only these three databases specified will not be accessed by "sp_rv_exec".
A maximum of 10 databases can be specified for "sp_rv_exec_include_db". To specify more than 10 databases, just call "sp_rv_exec_include_db" again: repeated calls are cumulative.

Executing "sp_rv_exec_include_db" without any parameters will clear the list of databases to be excluded from access and returns to the default of searching all databases. You can verify the current settings by running sp_rv_exec_list_db .

These settings are session-specific.


Known problems & issues:
  • Same as for "sp_rv_exec_include_db"


sp_rv_exec_list_db

Syntax:
sp_rv_exec_list_db [ "reset" ]
        
Description:
Displays current settings w.r.t. which databases will be accessed by "sp_rv_exec":
exec sp_rv_exec_list_db
        
This is based on what was previously specified through sp_rv_exec_include_db and sp_rv_exec_exclude_db.

All settings can be cleared and the default situation restored (access all databases), by specifying the parameter "reset" :
exec sp_rv_exec_list_db "reset"
        
Note that this is a session-specific setting.


Known problems & issues:
  • None.

 
 This document is located at www.sypron.nl/new_ssp_man.html