Search in your T-SQL objects

,

Ever searched in your code for a column name? or a tablename? This script does it all!

It not only search your SP and Functions code, but also checks if your searchcriteria is part of an objectname. It also shows you the modification date (if possible)

declare @SearchCriteria nvarchar(100)

set @SearchCriteria = '%%' ---------> enter criteria here

select    distinct 
        @SearchCriteria as ZoekCriteria
        , 'in code' as Classification
        , b.type_desc
        , object_name(a.id) "ObjectName" 
        , b.modify_date
from    syscomments a
inner    join sys.objects b
on        a.id = b.object_id
where    a.encrypted = 0
and        a.text like @SearchCriteria
union
select     @SearchCriteria
        , 'in code' as Classification
        , 'SYNONYM'
        , name
        , modify_date
from    sys.synonyms
where    base_object_name like @SearchCriteria
union
select     @SearchCriteria
        , 'in code' as Classification
        , 'CHECK_CONSTRAINT'
        , name 
        , modify_date
from    sys.check_constraints
where    definition like @SearchCriteria
union
select     @SearchCriteria
        , 'in code' as Classification
        , 'DEFAULT_CONSTRAINT'
        , name 
        , modify_date
from    sys.default_constraints
where    definition like @SearchCriteria
union
select     @SearchCriteria
        , 'as columnname' as Classification
        , 'COLUMN'
        , object_name(object_id) collate database_default
        , NULL
from    sys.columns 
where    name like @SearchCriteria
union
select     @SearchCriteria
        , 'in objectname' as Classification
        , type_desc collate database_default
        , name collate database_default
        , modify_date
from    sys.objects 
where    name like @SearchCriteria
order    by 1,2
;

Rate

4.33 (3)

Share

Share

Rate

4.33 (3)