Find Procedures and tables by column

,

The stored proc FindObjByColumn_sp, while simple, is extremely useful when making database changes or for any reason that you need to find any tables and stored procedures that contain a particular column.  It searches the system tables of a database to find the object containing the column.  In addition, the procedure automatically adds a wildcard so that the user does not have to know the complete name.  Finally, for stored procedures, it searches the text of the stored procedure and returns procedure names even if the name is used in a variable.

If exists (Select * from sysobjects where name = 'FindObjByColumn_sp' and type = 'P')
drop proc FindObjByColumn_sp 
go

Create Proc FindObjByColumn_sp@Column varchar(30) 
/******************************************************************************************************

Procedure Name: FindObjByColumn_sp
Purpose :Search the database for all tables and all procedures that use a specific column.
Called by:N/A

Input Params:@Column - stores the column name that user is searchin for
Output Params:None
Return Values:

Written By:PNE 08/04/2002
Syntax:FindObjByColumn_sp 'DistributionChannel'
Change history:

********************************************************************************************************/--SYNTAX:
AS
Declare @ssql varchar(100)

--Add wildcard so that seach can be performed even if only part of the column name is used
Select @Column = @Column + '%'

SET NOCOUNT ON

select @ssql = 'Tables that contain columns that begin with ' + @Column
select @ssql
print '----------------------------------------------------------'
SELECT convert(varchar(60), O.NAME) "Table Name" 
FROM SYSCOLUMNS C, SYSOBJECTS O, master.dbo.systypes T 
WHERE C.ID = O.ID AND C.xtype = T.xtype
AND C.NAME LIKE @Column
and O.Type = 'U'
group by o.name

--For stored procedures, add wildcard to beginning because column will be contained within text
Select @Column =  '%' + @Column 

select @ssql = 'Procs that contain the word ' + replace(@Column, '%', '')
select @ssql
print '----------------------------------------------------------'
select convert(varchar(60), O.NAME) "Proc Name"
from sysobjects o, syscomments c
where o.id = c.id
and c.text like @Column

SET NOCOUNT OFF

Rate

Share

Share

Rate