MS SQL Search within programmable objects (sp, trigger, view,...)

Below script searches keywords within programmable objects in all databases.

declare @keyword1 varchar(50) = 'SomeTable'
declare @keyword2 varchar(50) = 'SomeColumn'

create table #t
(
 [ReferencingDatabase] varchar(100),
 [ReferencingSchema] varchar(100),
 [ReferencingObject] varchar(100),
 [ReferencingObjectType] varchar(100),
 [HelpText] varchar(300)
)

declare @cmd1 varchar(8000)
set @cmd1 = '
use ?
if ''?'' not in (''distribution'', ''master'', ''model'', ''msdb'', ''tempdb'', ''mssqlsystemresource'')
begin
insert into #t
select
''?'' as [ReferencingDatabase],
s.name as [ReferencingSchema],
o.name as [ReferencingObject],
o.type_desc as [ReferencingObjectType],
''sp_helptext '''''' + s.Name + ''.'' + o.name + '''''''' as [Script]
from sys.syscomments c
left outer join sys.objects o on o.object_id = c.id
left outer join sys.schemas s on s.schema_id = o.schema_id
where
c.text like ''%' + @keyword1 + '%''
and c.text like ''%' + @keyword2 + '%''
end
'
exec sp_msforeachdb @cmd1
select * from #t
drop table #t

Find dependant objects in MS SQL

Below script finds dependant objects for a given object.


declare @server varchar(50) = 'sql server name'
declare @db varchar(50) = 'database name'
declare @schema varchar(50) = 'schema name'
declare @object varchar(50) = 'object name'

create table #t
(
[ReferencingDatabase] varchar(100),
[ReferencingSchema] varchar(100),
[ReferencingObject] varchar(100),
[ReferencingObjectType] varchar(100)
)

declare @cmd1 varchar(8000)
set @cmd1 = '
if ''?'' not in (''distribution'', ''master'', ''model'', ''msdb'', ''tempdb'', ''mssqlsystemresource'')
begin
insert into #t
select
 ''?'' as [ReferencingObjectDatabase],
 s.name as [ReferencingObjectSchema],
 o.name as [ReferencingObjectName],
 o.type_desc as [ReferencingObjectType]
from ?.sys.sql_expression_dependencies d
inner join ?.sys.objects o on o.object_id = d.referencing_id
inner join ?.sys.schemas s on s.schema_id = o.schema_id
where d.referenced_entity_name = ''' + @object + '''
and (d.referenced_server_name = ''' + @server + ''' or d.referenced_server_name is null)
and (d.referenced_database_name = ''' + @db + ''' or d.referenced_database_name is null)
and (d.referenced_schema_name = ''' + @schema + ''' or d.referenced_schema_name = '''' or d.referenced_schema_name is null)
end
'

exec sp_msforeachdb @cmd1
select * from #t
drop table #t