Database | Proc | Application | Created | Links |
sybsystemprocs | sp_lookup ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/lock */ 4 5 create procedure sp_lookup 6 @typearg int, 7 @owname varchar(255) = "%", /* owner of the desired objects (except indexes) */ 8 @obname varchar(255) = "%", /* name of the desired objects (including indexes) */ 9 @indtab varchar(255) = "%", /* table associated with the desired index */ 10 @indown varchar(255) = "%" /* owner of the desired index */ 11 as 12 create table #ddtab 13 (name varchar(255), 14 owner varchar(255), 15 type int, 16 indtab varchar(255)) 17 18 declare @table int 19 declare @view int 20 declare @index int 21 declare @sproc int 22 declare @trgr int 23 declare @dflt int 24 declare @rule int 25 declare @dtype int 26 declare @db int 27 declare @report int 28 29 30 31 if @@trancount = 0 32 begin 33 set chained off 34 end 35 36 set transaction isolation level 1 37 38 select @table = 1, @view = 2, @index = 4, @sproc = 8, @trgr = 16, @dflt = 32, 39 @rule = 64, @dtype = 128, @db = 256, @report = 512 40 41 if (@typearg & @table = @table) 42 insert into #ddtab 43 select name, user_name(uid), @table, '' from dbo.sysobjects 44 where name like @obname 45 and user_name(uid) like @owname 46 and (sysstat & 7 = 1 /* system table */ 47 or sysstat & 7 = 3) /* user table */ 48 49 50 if (@typearg & @view = @view) 51 insert into #ddtab 52 select name, user_name(uid), @view, '' from dbo.sysobjects 53 where name like @obname 54 and user_name(uid) like @owname 55 and sysstat & 7 = 2 /* view */ 56 57 if (@typearg & @index = @index) 58 insert into #ddtab 59 select i.name, user_name(o.uid), @index, o.name 60 from dbo.sysindexes i, dbo.sysobjects o 61 where i.name like @obname 62 and i.indid > 0 63 and i.id = o.id 64 and user_name(o.uid) like @indown 65 and o.name = object_name(o.id) 66 and o.name like @indtab 67 68 if (@typearg & @report = @report) 69 insert into #ddtab 70 select name, user_name(uid), @report, '' from dbo.sysobjects 71 where name like @obname 72 and user_name(uid) like @owname 73 and sysstat & 7 = 4 /* sproc */ 74 and userstat & - 32768 = - 32768 /* report */ 75 76 if (@typearg & @sproc = @sproc) 77 begin 78 /* 79 ** If we also wanted reports, don't pick up reports again as sprocs. 80 */ 81 if (@typearg & @report = @report) 82 begin 83 insert into #ddtab 84 select name, user_name(uid), @sproc, '' from dbo.sysobjects 85 where name like @obname 86 and user_name(uid) like @owname 87 and sysstat & 7 = 4 /* stored procedure */ 88 and userstat & - 32768 != - 32768 /* report */ 89 end 90 else 91 begin 92 insert into #ddtab 93 select name, user_name(uid), @sproc, '' from dbo.sysobjects 94 where name like @obname 95 and user_name(uid) like @owname 96 and sysstat & 7 = 4 /* stored procedure */ 97 end 98 end 99 100 if (@typearg & @trgr = @trgr) 101 insert into #ddtab 102 select name, user_name(uid), @trgr, '' from dbo.sysobjects 103 where name like @obname 104 and user_name(uid) like @owname 105 and sysstat & 7 = 0 /* trigger */ 106 107 if (@typearg & @dflt = @dflt) 108 insert into #ddtab 109 select name, user_name(uid), @dflt, '' from dbo.sysobjects 110 where name like @obname 111 and user_name(uid) like @owname 112 and sysstat & 7 = 6 /* default */ 113 114 if (@typearg & @rule = @rule) 115 insert into #ddtab 116 select name, user_name(uid), @rule, '' from dbo.sysobjects 117 where name like @obname 118 and user_name(uid) like @owname 119 and sysstat & 7 = 7 /* rule */ 120 121 if (@typearg & @dtype = @dtype) 122 insert into #ddtab 123 select name, user_name(uid), @dtype, '' from dbo.systypes 124 where name like @obname 125 and user_name(uid) like @owname 126 127 if (@typearg & @db = @db) 128 insert into #ddtab 129 select name, suser_name(suid), @db, '' 130 from master.dbo.sysdatabases 131 where (name like @obname) 132 and suser_name(suid) like @owname 133 134 exec sp_autoformat @fulltabname = #ddtab, 135 @orderby = "order by name" 136 137 drop table #ddtab 138 139 return (0) 140
exec sp_procxmode 'sp_lookup', 'AnyMode' go Grant Execute on sp_lookup to public go
DEPENDENCIES |
PROCS AND TABLES USED calls proc sybsystemprocs..sp_autoformat ![]() reads table tempdb..syscolumns (1) ![]() calls proc sybsystemprocs..sp_namecrack ![]() read_writes table tempdb..#colinfo_af (1) reads table master..syscolumns (1) ![]() reads table tempdb..systypes (1) ![]() reads table master..systypes (1) ![]() calls proc sybsystemprocs..sp_autoformat ![]() reads table master..sysdatabases (1) ![]() reads table sybsystemprocs..sysindexes ![]() reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..systypes ![]() writes table tempdb..#ddtab (1) |