Find database objects that contain a string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Find database objects that contain a string

Author

Topic

datagod
Starting Member

37 Posts

Posted - 2006-05-05 : 16:16:12

[CODE]
-- 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

[/CODE]

datagod
Starting Member

37 Posts

Posted - 2006-05-05 : 16:19:02

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.

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-05 : 16:26:08

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

datagod
Starting Member

37 Posts

Posted - 2006-05-05 : 16:32:20

Thanks!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-02-14 : 03:00:54

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources