SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Find database objects that contain a string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

datagod
Starting Member

37 Posts

Posted - 05/05/2006 :  16:16:12  Show Profile  Reply with Quote


-- This stored procedure will let you search through your database
-- to find various objects that contain a particular string.
-- For example, you may want to see all tables and views that contain
-- a particular column.


use master
IF (object_id('sp_FindReferences') IS NOT NULL)
BEGIN
  PRINT 'Dropping: sp_FindReferences'
  DROP procedure sp_FindReferences
END
PRINT 'Creating: sp_FindReferences'
GO
CREATE PROCEDURE sp_FindReferences
(
  @string         varchar(1000) = '',
  @ShowReferences char(1)       = 'N'
)
AS
/****************************************************************************/
/*                                                                          */
/* TITLE:   sp_FindReferences                                               */
/*                                                                          */
/* DATE:    18 February, 2004                                               */
/*                                                                          */
/* AUTHOR:  WILLIAM MCEVOY                                                  */
/*                                                                          */
/****************************************************************************/
/*                                                                          */
/* DESCRIPTION:  SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */
/*                                                                          */
/****************************************************************************/
set nocount on

declare @errnum         int         ,
        @errors         char(1)     ,
        @rowcnt         int         ,
        @output         varchar(255)

select  @errnum         = 0         ,
        @errors         = 'N'       ,
        @rowcnt         = 0         ,
        @output         = ''        

/****************************************************************************/
/* INPUT DATA VALIDATION                                                    */
/****************************************************************************/


/****************************************************************************/
/* M A I N   P R O C E S S I N G                                            */
/****************************************************************************/

-- Create temp table to hold results
create table #Results
(
  Name        varchar(55),
  Type        varchar(12),
  DateCreated datetime,
  ProcLine    varchar(4000)
)


IF (@ShowReferences = 'N')
BEGIN
  insert into #Results
  select distinct
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         ''
    from sysobjects  SO
    join syscomments SC on SC.id = SO.id
   where SC.text like '%' + @string + '%'
  union
  select distinct
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         ''
    from sysobjects  SO
   where SO.name like '%' + @string + '%'
  union
  select distinct
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         ''
    from sysobjects  SO
    join syscolumns SC on SC.id = SO.ID
   where SC.name like '%' + @string + '%'
   order by 2,1
END
ELSE
BEGIN
  insert into #Results
  select 
         'Name'      = convert(varchar(55),SO.name),
         'Type'      = SO.type,
         crdate,
         'Proc Line' = text
    from sysobjects  SO
    join syscomments SC on SC.id = SO.id
   where SC.text like '%' + @string + '%'
  union
  select 
         'Name'      = convert(varchar(55),SO.name),
         'Type'      = SO.type,
         crdate,
         'Proc Line' = ''
    from sysobjects  SO
   where SO.name like '%' + @string + '%'
  union
  select 
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         'Proc Line' = ''
    from sysobjects  SO
    join syscolumns SC on SC.id = SO.ID
   where SC.name like '%' + @string + '%'
   order by 2,1
END


IF (@ShowReferences = 'N')
BEGIN
  select Name,
         'Type' = Case (Type)
                    when 'P'  then 'Procedure'
                    when 'TR' then 'Trigger'
                    when 'X'  then 'Xtended Proc'
                    when 'U'  then 'Table'
                    when 'C'  then 'Check Constraint'
                    when 'D'  then 'Default'
                    when 'F'  then 'Foreign Key'
                    when 'K'  then 'Primary Key'
                    when 'V'  then 'View'
                    else Type
                  end,
         DateCreated
    from #Results
    order by 2,1
END
ELSE
BEGIN
  select Name,
         'Type' = Case (Type)
                    when 'P'  then 'Procedure'
                    when 'TR' then 'Trigger'
                    when 'X'  then 'Xtended Proc'
                    when 'U'  then 'Table'
                    when 'C'  then 'Check Constraint'
                    when 'D'  then 'Default'
                    when 'F'  then 'Foreign Key'
                    when 'K'  then 'Primary Key'
                    when 'V'  then 'View'
                    else Type
                  end,
         DateCreated,
         ProcLine
    from #Results
    order by 2,1
END

drop table #Results

GO
IF (object_id('sp_FindReferences') IS NOT NULL)
  PRINT 'Procedure created.'
ELSE
  PRINT 'Procedure NOT created.'
GO




Edited by - datagod on 05/05/2006 16:33:09

datagod
Starting Member

37 Posts

Posted - 05/05/2006 :  16:19:02  Show Profile  Reply with Quote
Gee...not vey pleased with how this forum stripped out all my extra space...the proc is VERY neatly written...but that does not come across here.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/05/2006 :  16:26:08  Show Profile  Reply with Quote
Dude, edit the sproc and put code tags arounf your code, like this

[code ]

[/code ]

just remove the trailing space in the tag

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

datagod
Starting Member

37 Posts

Posted - 05/05/2006 :  16:32:20  Show Profile  Reply with Quote
Thanks!

Edited by - datagod on 05/05/2006 16:33:25
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 02/14/2013 :  03:00:54  Show Profile  Reply with Quote
Man...close to 7 years on and I'm still using this script several times a week. I don't think any scripts I've ever come across has been this useful. Just wanted you to know

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000