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:
To find all identity columns in the
exec sp_rv_findobject "coltype=%identity%"
To find only the tables which
contain an identity column:
To find all columns of a
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
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:
Note the procedure sp_rv_dropobject
being invoked here. This is a procedure to
be careful with, because it drops just about anything you
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:
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:
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
For example, to count the number of
tables in every database in the server:
"exec=immediate", "execarg=select '#
Tables in database DB=' + str(count(*),5) from
This will execute the
command in every database and display
the number of tables in each database. The parameter
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 ISUG Tech Journal article
discussing background and
architecture of these SSPs;
- the manual
pages for the SSPs;
- the FAQ
for the SSPs.