If we now the parameter name and want to know the Objects using that parameter then in that case we can use the below stored procedure to retrive the Object Names
/*********************************************************************** * Author : V.U.M.Sastry Sagi * Date : 11/16/2011 * Purpose: Returns all Objects including SPs and Functions based on given parameter ***********************************************************************/ CREATE PROCEDURE GetObjectNamesBasedOnParameter ( @prameter VARCHAR(100) ) AS BEGIN SELECT dbo.sysobjects.name AS ObjName , dbo.sysobjects.xtype AS ObjType , dbo.syscolumns.name AS ColName , dbo.syscolumns.colorder AS ColOrder , dbo.syscolumns.length AS ColLen , dbo.syscolumns.colstat AS ColKey , dbo.systypes.xtype FROM dbo.syscolumns INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype WHERE ( dbo.syscolumns.name = @prameter ) AND ( dbo.systypes.status <> 1 ) ORDER BY dbo.sysobjects.name , dbo.syscolumns.colorder END
