Monday, 8 February 2010

Code to Search SQL Server Objects for a Text String...

declare @SearchFor varchar(100);
set @SearchFor = 'blah blah blah';

if left(@SearchFor, 1) != '%' set @SearchFor = '%' + @SearchFor;
if right(@SearchFor, 1) != '%' set @SearchFor = @SearchFor + '%';

select as SchemaName
, as ObjectName
,o.type_desc as ObjectTypeDesc
,o.is_ms_shipped as IsMSShipped
,'exec sp_helptext ''[' + + '].[' + + ']'';' as GetCode
sys.sql_modules sm
inner join sys.objects o
on sm.object_id = o.object_id
inner join sys.schemas s
on o.schema_id = s.schema_id
definition like @SearchFor -- What we are looking for
and o.is_ms_shipped = 0 -- Not a Microsoft bit of code
order by

Edit - 20101015: It's worth nothing that this is not the most efficient piece of code, and that there is a free RedGate product out there that will search your SQL instance for you (SSMS integrated):

No comments:

Post a Comment

Migrating (and Open-Sourcing) an Historical Codebase: SVN-to-Git

I have a SVN repo on my local machine that I have been shoving stuff into since before I knew how to use revision control systems properly (...