(back)
 
Sybase ASE
11.0 11.5 11.9 12.0 12.5 15.x
Last updated: 06 December 2000
Examples of "sp_rv_findobject"
 
Here are some examples of what you can do with sp_rv_findobject . This list is by no means complete, as the number of possible applications is huge.



To find all objects in the server, having a name starting with "a" and not owned by the dbo :
exec sp_rv_findobject "name=a%", "owner!=dbo"
        


To find all views in the server created in January 1999:
exec sp_rv_findobject
            "type=V",
            "created>=01-Jan-1999",
            "created<=01-Feb-1999"
        


To find all identity columns in the server:
exec sp_rv_findobject "coltype=%identity%"
        

To find only the tables which contain an identity column:
exec sp_rv_findobject
            "coltype=%identity%",
            "display=DB.OW.NM"
        

To find all columns of a user-defined datatype:
exec sp_rv_findobject "coltype=%<%"
        


It is also possible to perform actions on the objects found by specifying a plug-in stored procedure through the "exec=" option. Suppose you want to make a list of the size and rowcount of all tables starting with "a" :
exec sp_rv_findobject "name=a%", "exec=sp_spaceused"
        
Or maybe you want to drop all unused user-defined datatypes that are littering around your server:
exec sp_rv_findobject
            "type=UDD", "exec=sp_droptype",
            "execarg=NM"
        
Note: the option "execarg=NM" causes only the datatype name to be passed to "sp_droptype", as this procedure can't handle a fully qualified name. Also note: you can safely issue "sp_droptype" on a datatype which is still in use, as an error message will be generated.



In my own case, I always create tables, procedures, views etc. for quickly tests with names like "t1", "p1", "p2", etc. After some time, this gets messy, with lots of these objects hanging around different databases. To clean these all up, I simply do the following:
exec sp_rv_findobject
            "name=[a-z][0-9]",
            "exec=sp_rv_dropobject"
        
Note the procedure sp_rv_dropobject being invoked here. This is a procedure to be careful with, because it drops just about anything you feed it...

Suppose John Smith has left the company and did not clean up many tables, views etc. he left hanging around the various databases in your server. To clean up all his objects, you could simply run the following command:
sp_rv_findobject
            "owner=jsmith",
            "exec=sp_rv_dropobject"
        


To make a list of all varchar(10) columns in your server, and to store the result in a database table rather than display it on the screen:
exec sp_rv_findobject
            "coltype=%varchar(10)%",
            "output=my_results_table"
        

This will create a table "tempdb..my_result_table', holding the search results which would normally be displayed on the screen.



When running ASE 12.0 or later, you can use the "execute immediate" feature of ASE to flexibly create and execute commands. For this, you must specify "EXEC=IMMEDIATE" and "EXECARG=-some-format-string-". EXECARG can contains the placeholders DB, OW, NM, ID and CL, which are replaced with the database name, owner name,object name, object ID or column name, respectively. The resulting string is then executed through "execute immediate".

For example, to count the number of tables in every database in the server:
exec sp_rv_findobject
            "name=sysobjects",
            "exec=immediate", "execarg=select '#
            Tables in database DB=' + str(count(*),5) from
            sysobjects" 
        
This will execute the select count(*) command in every database and display the number of tables in each database. The parameter name=sysobjects has been added to make it execute only once in every database.



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 manual pages for the SSPs;
  • the FAQ for the SSPs.





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

 Copyright © 1998-2014 Rob Verschoor/Sypron B.V. 
 All rights reserved. All trademarks are acknowledged.