USE [Impresario]
-- search stored procedures for TX_CUST_MEMBERSHIP table
SELECT OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%TX_CUST_MEMBERSHIP%'
	and (OBJECT_NAME(object_id) like 'L%'			
		 OR OBJECT_NAME(object_id) like 'SO%')		-- 
order by OBJECT_NAME(object_id)

-- search stored procedures for TX_CONT_MEMB table
SELECT OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%TX_CONT_MEMB%'
	and (OBJECT_NAME(object_id) like 'L%'			
		 OR OBJECT_NAME(object_id) like 'SO%')		--
order by OBJECT_NAME(object_id)

-- search report tables for Memb in Report Parameters
select category_desc = c.description, rp.name, rp.psr_file, rp.report_type, rp.inactive -- parameter_name = grp.description, 
from gooesoft_request r
	left outer join gooesoft_request_parameter p on r.report_id = p.report_id and r.id = p.request_id
	join gooesoft_report rp on r.report_id = rp.id
	left outer join gooesoft_report_parameter grp on rp.id = grp.report_id and p.parameter_id = grp.id
	join gooesoft_report_category c on rp.category = c.id
where grp.description like '%Memb%'
	and rp.name like 'SO%'
group by c.description, rp.name, rp.psr_file, rp.report_type, rp.inactive -- grp.description
order by c.description, rp.name

-- seach for Memb for criteria elements, attributes, custom table fields
select * from T_KEYWORD where description like '%Memb%'

-- search for Memb in output elements
select q.description, q.data_select, Group_desc = g.description, g.data_from, g.data_where
from TR_QUERY_ELEMENT q
	join TR_QUERY_ELEMENT_GROUP g on q.group_id = g.id
where (q.description like '%Memb%' 
	or g.description like '%Memb%')	-- 27
	and q.inactive = 'N'			-- 24


-- search for Memb in SQL Jobs
USE [msdb]

SELECT s.step_id as 'Step ID', j.[name] as 'SQL Agent Job Name', s.database_name as 'DB Name', s.command as 'Command'
FROM   msdb.dbo.sysjobsteps AS s
	INNER JOIN msdb.dbo.sysjobs AS j ON  s.job_id = j.job_id
WHERE  j.[name] not like 'S%'
	and s.database_name = 'Impresario'
	and s.command LIKE '%Memb%'

SELECT *
FROM msdb.dbo.sysjobs j 
WHERE  j.[name] not like 'S%'
	
-- The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.