存储过程与表的关系

1.查询某个表被哪些存储过程（以下简称 SP）使用 :

 select distinct object_name(id) fromsyscomments where id in

 (select object_id from sys.objects where type='P') and text like'%TableName%'

 

2.查找那些过程对该表做了更新操作：

 select distinct object_name(id) fromsyscomments where id in

 (select object_id from sys.objects where type='P') and text like'%update tablename%'

 

3.查询当前数据库中所有SP：

 select name as 存储过程  fromsysobjects where type = 'P'

 

 

 

---------------------------------------------------------------------------使用举例------------------------------------------------------------------------------

 

 SELECT DISTINCT         OBJECT_NAME(id)  FROM   syscomments  WHERE  id IN ( SELECT  object_id                 FROM    sys.objects                 WHERE   type = 'P' )         AND text LIKE '%t_Supplier%'   go

 SELECT DISTINCT         OBJECT_NAME(id) FROM syscomments  WHERE  id IN ( SELECT  object_id                 FROM    sys.objects                 WHERE   type = 'P' )         AND text LIKE '%update t_Supplier%'

 

 SELECT *  FROM   dbo.t_TableDescription  WHERE  FTableName LIKE '%供应商%'

--存储过程 /* yxyz_YFKHZ_DemoNew_czq GetItemDetailByID p_ICPlan_ICClassProfileAdjust_ColWidth_ColOrder PoSupplyerPriceAnalyse GetItemDetail p_UpdateSupplyInfoByImpOrd Rpt120 PrintItemClass p_BM_PurPayBudgetImp yxyz_YFMX Rpt170 yxyz_YFKHZ_Demo p_UpdateSupplierFID PoOrderAbcAnalyse p_BM_PurchaseBudgetImport GetItemDetailByName Rpt119 PRC_ShopGenNoteCitation sp_K3_insertoutsourceWarehousing sp_tb_icstockbill_1Auto_ICPurchase PRC_GenNoteCitation GetBaseDetail ICScmInfoUsed PoSupplyerAbcAnalyse */