quote:

---

Originally posted by TG

One way (that will take a little effort) is the following statements. It could give you some false positives if your column is a common name like [code].

use pubs
select distinct routine_name, routine_type as object from information_schema.routines where routine_definition like '%pub_id%'
union
select table_name, 'table/view' as object from information_schema.columns where column_name = 'pub_id'

Be One with the Optimizer
TG

---

quote:

---

Originally posted by Michael Valentine Jones

quote:

---

Originally posted by TG

One way (that will take a little effort) is the following statements. It could give you some false positives if your column is a common name like [code].

use pubs
select distinct routine_name, routine_type as object from information_schema.routines where routine_definition like '%pub_id%'
union
select table_name, 'table/view' as object from information_schema.columns where column_name = 'pub_id'

Be One with the Optimizer
TG

---

information_schema.routines routine_definition only shows the first 4000 charactere of the procedure or function code, and does not include trigger code or view definitions at all. I think it is better to do a direct search of syscomments.

If you are only dealing with one database, just script all objects out to a single file, and search that in an editor.

CODO ERGO SUM

---

USE [databasename]

DECLARE 
  @string varchar(1000),
  @ShowReferences char(1)

SET @string = 'mysearchterm' --> searchstring

SET @ShowReferences = 'N'
/****************************************************************************/
/*                                                                          */
/* 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
DECLARE @Results table
(
  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