 Sybase Tips & Tricks by Rob Verschoor | |||
|
|
|
||||||||||||||||||||||||||
|
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" :
|
||||||||||||||||||||||||||
print version
|