How to find all db objects containing string in Sybase.

Question

Sometimes I need to find some strings inside DB usually it is just host-name or ip address.

Is there any script which finds string in all Sybase db objects (or at least in all tables) that I have access to.

Answer 1

None that I know of natively. You could perhaps roll a procedure which queries the system tables to get a listing of objects, and then have a cursor go through and query those objects for your given search criteria.

Answer 2

The query on sysobjects + cursor + EXECUTE works. kinda sorta.

sp__map_object 'SELECT * FROM %O where name like "bar%"', 'foo_%'

There are so many bugs/issues/worries in the code that I am reluctant to post it but I have a certain amount of sympathy for a fellow Sybase sufferer.

Rob Verschoor has a bunch of nice stored procedures at http://www.sypron.nl/new_ssp_dwn.html

pjjH

289:1> ? sp__map_object
(1 row affected)
/*
 *
 * BE CAREFUL WHEN RUNNING THIS!!!
 *
 * See list of dire warnings in sp__map_db.sql
 *
 * Run the given command on the stored procedure for each object
 * matching @object_pat and replacing the first instance of %O with that
 * name.
 *
 * Just do this in Perl. Or refactor the sql so that it works for some sane
 * cross-product of users,logins, databases and objects.
 *
 * Paul Harrington 
 *
 */
CREATE PROCEDURE sp__map_object
  @sql             VARCHAR(255) = 'sp__help',
  @objpat          VARCHAR(32)  = NULL,
  @type            VARCHAR(2)   = 'U',
  @do_it           INT          = 1,
  @verbose         INT          = 0
AS
BEGIN
  SET NOCOUNT ON
  DECLARE object_cursor CURSOR
  FOR
    SELECT DISTINCT name
    FROM   sysobjects
    WHERE  name LIKE @objpat
      AND  type LIKE @type
  DECLARE @munged_sql VARCHAR(255)

  DECLARE @object_name varchar(64)
  OPEN object_cursor
  FETCH object_cursor INTO @object_name
  WHILE @@sqlstatus = 0
  BEGIN
    IF CHARINDEX('%O', @sql)  0
    SELECT @munged_sql = SUBSTRING(@sql, 1, CHARINDEX('%O', @sql) - 1) +
                  @object_name +
                  SUBSTRING(@sql, CHARINDEX('%O', @sql) + 2, 255)
    ELSE
      SELECT @munged_sql = @sql

    IF (@do_it = 0 OR @verbose = 1)
    BEGIN
       DECLARE @msg varchar(255)
       SELECT @msg = @munged_sql
       SELECT @msg
    END

    IF @do_it = 1
        EXECUTE(@munged_sql)
    FETCH object_cursor INTO @object_name
  END
  CLOSE object_cursor
  DEALLOCATE CURSOR object_cursor
  SET NOCOUNT OFF
END

go