출처 : swynk.com내용 : 자신이 만든 SP나 테이블을 찾고 싶을때 유용한 저장 프로시져 입니다.I've written stored procedures in the past where I could remember some of the code they contained, but not the name of the stored procedure. I developed this little stored procedure to help find those elusive stored procedures based on the code in the stored procedure. It searches the sysobjects and syscomments tables to find any occurence of a text string. It sorts the return set by object name. (Supports SQL 2000, SQL 7.0, and possibly SQL 6.5) --생성구문CREATE procedure Where_Am_I@cString varchar(1000)AS set nocount on Select @cString = 'select substring( o.name, 1, 35 ) as Object, count(*) as Occurences, ' + 'case ' + ' when o.xtype = ''D'' then ''Default'' ' + ' when o.xtype = ''F'' then ''Foreign Key'' ' + ' when o.xtype = ''P'' then ''Stored Procedure'' ' + ' when o.xtype = ''PK'' then ''Primary Key'' ' + ' when o.xtype = ''S'' then ''System Table'' ' + ' when o.xtype = ''TR'' then ''Trigger'' ' + ' when o.xtype = ''U'' then ''User Table'' ' + ' when o.xtype = ''V'' then ''View'' ' + 'end as Type ' + 'from syscomments c join sysobjects o on c.id = o.id ' + 'where patindex( ''%' + @cString + '%'', c.text ) > 0 ' + 'group by o.name, o.xtype ' + 'order by o.xtype, o.name' Execute( @cString )Return--사용 방법exec Where_Am_I 'dbo'
