How to find all db objects containing string in Sybase

A request is made for sysobjects + cursor + EXECUTE. kinda sorta.

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

There are a lot of bugs / issues / issues in the code that I don't want to post, but I have a certain liking for a sympathetic patient Sybase.

Rob Vershhor has a bunch of good 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