11/16/2011
Get Object Names Based on Parameter
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