SQL Server traverses the database file to find all stored procedures that use a table/stored procedure

SQL Server traverses the database file to find all stored procedures that use a table/stored procedure.

1 CREATE ProcedureSp_getproc2 @Object_Name Varchar( -)      3  as      4 SetNocount on      5   6 DECLARE @tmptable TABLE    7 (    8Idint IDENTITY(1,1),    9 [DataBase] Varchar( -),  TenNameVarchar( -),   OneTypeVarchar( -),   ACreate_dateDatetime   - )     -    the DECLARE @SQLText TABLE     - (     -Idint IDENTITY(1,1),     -SQLTextVarchar(Max)   + )     -    + if Charindex('#',@Object_Name)> 0   A begin    at   Select @Object_Name = Replace(@Object_Name,'#',"')   -    -   Insert  into @SQLText   -     Select '       - Select" " +Name+ " "[DataBase], CONVERT (VARCHAR ($), a.name COLLATE chinese_prc_ci_as) Name, A.type, A.create_date -  from' +Name+ '. Sys.all_objects a in Where a.name like"'%' + @Object_Name + '%" "       -        fromsys.databaseswheredatabase_id> 4       to End   + Else begin   -   Insert  into @SQLText   the     Select '       * Select" " +Name+ " "[DataBase], CONVERT (VARCHAR ($), a.name COLLATE chinese_prc_ci_as) Name, A.type, A.create_date $  from' +Name+ '. Sys.all_objects A,' +Name+ '. sys.syscomments bPanax Notoginseng Where a.object_id = b.id and B.text like"'%' + @Object_Name + '%" "       -        fromsys.databaseswheredatabase_id> 4       the End   +    A Declare @ID Int,@MID Int,@SQl Varchar(Max)   the Select @ID = 1,@MID = MAX(ID) from @SQLText   +  while @ID <= @MID   - begin    $   Select @SQl =SQLText from @SQLText WhereId= @ID   $   Insert  into @tmptable Exec(@SQl)    -   Select @ID = @ID + 1   - End   the    - Select *  from @tmptable Order  byId

View Code

SQL Server traverses the database file to find all stored procedures that use a table/stored procedure