Database | Proc | Application | Created | Links |
sybsystemprocs | sp_depends ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/defaultlanguage */ 4 5 /* 6 ** Messages for "sp_depends" 17460 7 ** 8 ** 17460, "Object must be in the current database." 9 ** 17461, "Object does not exist in this database." 10 ** 17462, "Things the object references in the current database." 11 ** 17463, "Things inside the current database that reference the object." 12 ** 17464, "Object doesn't reference any object and no objects reference it." 13 ** 17465, "The specified column (or all columns, if none was specified) in %1! has no dependencies on other objects, and no other object depends on any columns from it." 14 ** 17466, "Dependent objects that reference column %1!." 15 ** 17467, "Dependent objects that reference all columns in the table. Use sp_depends on each column to get more information." 16 ** 17468, "Columns referenced in stored procedures, views or triggers are not included in this report." 17 ** 17469, "Tables that reference this object: " 18 ** 19986, "The dependencies of the stored procedure cannot be determined until the first successful execution." 19 */ 20 21 /* 22 ** IMPORTANT NOTE: 23 ** This stored procedure uses the built-in function object_id() in the 24 ** where clause of a select query. If you intend to change this query 25 ** or use the object_id() or db_id() builtin in this procedure, please read the 26 ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules 27 ** pertaining to object-id's and db-id's outlined there, are followed. 28 */ 29 create procedure sp_depends 30 @objname varchar(767) /* the object we want to check */ 31 , @column_name varchar(255) = null /* the column we want to check */ 32 as 33 34 declare @found_some bit /* flag for table dependencies found */ 35 , @msg varchar(1024) 36 , @sptlang int /* current sessions language */ 37 , @length int 38 , @colid int /* column id from syscolumns */ 39 , @objid int 40 41 if @@trancount = 0 42 begin 43 set chained off 44 end 45 46 set transaction isolation level 1 47 set nocount on 48 49 /* 50 ** Make sure the @objname is local to the current database. 51 */ 52 if @objname like "%.%.%" and 53 substring(@objname, 1, charindex(".", @objname) - 1) != db_name() 54 begin 55 /* 56 ** 17460, "Object must be in the current database." 57 */ 58 raiserror 17460 59 return (1) 60 end 61 62 /* 63 ** See if @objname exists. 64 */ 65 select @objid = object_id(@objname) 66 if not exists (select id 67 from sysobjects 68 where id = @objid) 69 begin 70 /* 71 ** 17461, "Object does not exist in this database." 72 */ 73 raiserror 17461 74 return (1) 75 end 76 77 /* 78 ** If a column name is provided, check that it exists in the said table. 79 */ 80 if @column_name is not null 81 begin 82 select @colid = colid 83 from syscolumns 84 where id = @objid and name = @column_name 85 86 if (@colid is NULL) 87 begin 88 /* 89 ** 17563, "The table does not have a column named '%1!'." 90 */ 91 raiserror 17563, @column_name 92 return (1) 93 end 94 end 95 96 /* 97 ** If procedure was created with deferred name resolution and 98 ** it was not executed already then indicate that dependencies 99 ** will be calculated after the first successfully execution. 100 */ 101 if exists (select type 102 from sysprocedures 103 where id = @objid and type = 2048) 104 begin 105 /* 106 ** 19986, "The dependencies of the stored procedure cannot be 107 ** determined until the first successful execution." 108 */ 109 exec sp_getmessage 19986, @msg output 110 print @msg 111 112 return (0) 113 end 114 115 /* 116 ** Initialize @found_some to indicate that we haven't seen any dependencies. 117 */ 118 select @found_some = 0 119 120 /* 121 ** Print out the particulars about the local dependencies. 122 */ 123 if exists (select * 124 from sysdepends 125 where id = @objid) 126 begin 127 /* 128 ** 17462, "Things the object references in the current database." 129 */ 130 exec sp_getmessage 17462, @msg output 131 print @msg 132 133 select @sptlang = @@langid 134 if @@langid != 0 135 begin 136 if not exists ( 137 select * from master.dbo.sysmessages where error 138 between 17010 and 17014 139 and langid = @@langid) 140 select @sptlang = 0 141 else 142 if not exists ( 143 select * from master.dbo.sysmessages where error 144 between 17100 and 17109 145 and langid = @@langid) 146 select @sptlang = 0 147 end 148 149 /* Check if this is a sqlj proc/func. If it is then the object 150 ** will reference only a java class. 151 */ 152 if exists (select * 153 from sysdepends d, sysxtypes x 154 where d.id = @objid 155 and d.depid = x.xtid) 156 157 begin 158 select object = x.xtname, 159 type = "java class" 160 into #depend1result 161 from sysdepends d, 162 sysxtypes x 163 where d.id = @objid 164 and d.depid = x.xtid 165 exec sp_autoformat @fulltabname = #depend1result, 166 @selectlist = "object, 'java class' = type" 167 drop table #depend1result 168 end 169 else 170 begin 171 select object = s.name + "." + o.name, 172 type = convert(char(16), m0.description), 173 updated = convert(char(10), m1.description), 174 selected = convert(char(10), m2.description) 175 into #depend2result 176 from sysobjects o, master.dbo.spt_values v, 177 sysdepends d, master.dbo.spt_values u, 178 master.dbo.spt_values w, 179 sysusers s, 180 master.dbo.sysmessages m0, 181 master.dbo.sysmessages m1, 182 master.dbo.sysmessages m2 183 where o.id = d.depid 184 and o.sysstat & 15 = v.number and v.type = 'O' 185 and v.msgnum = m0.error 186 and isnull(m0.langid, 0) = @sptlang 187 and u.type = 'B' and u.number = d.resultobj 188 and u.msgnum = m1.error 189 and isnull(m1.langid, 0) = @sptlang 190 and w.type = 'B' and w.number = d.readobj 191 and w.msgnum = m2.error 192 and isnull(m2.langid, 0) = @sptlang 193 and d.id = @objid 194 and o.uid = s.uid 195 exec sp_autoformat @fulltabname = #depend2result 196 drop table #depend2result 197 end 198 select @found_some = 1 199 end 200 201 /* 202 ** Now check for things that the objects depends upon 203 */ 204 if exists (select * 205 from sysdepends 206 where depid = @objid) 207 begin 208 /* 209 ** 17463, "Things inside the current database that reference the object." 210 */ 211 exec sp_getmessage 17463, @msg output 212 print @msg 213 select distinct 214 object = s.name + "." + o.name, 215 type = (case 216 when (o.type = "IT") 217 then "instead of " 218 else null 219 end) 220 + (v.name) 221 into #depend3result 222 from sysobjects o, master.dbo.spt_values v, 223 sysdepends d, sysusers s 224 where o.id = d.id 225 and o.sysstat & 15 = v.number 226 and v.type = 'O' 227 and d.depid = @objid 228 and o.uid = s.uid 229 -- Match on column name, if one is provided. 230 and (@column_name IS NULL 231 or exists 232 (select * 233 from master..spt_values v2 234 where v2.type = "P" 235 and v2.number <= 1024 236 and @column_name = col_name(d.depid, v2.number) 237 and ((convert(tinyint, substring(isnull(d.columns, 0x1), 238 v2.low, 1)) 239 & v2.high) != 0) 240 ) 241 ) 242 exec sp_autoformat @fulltabname = #depend3result, 243 @selectlist = "object, type", 244 @orderby = "order by object" 245 drop table #depend3result 246 select @found_some = 1 247 end 248 249 /* If this object aplied to a column in a table we want to 250 ** display the table name that references it (used for rules and 251 ** defaults). 252 */ 253 254 if exists (select * 255 from syscolumns 256 where @objid IN (cdefault, domain, accessrule)) 257 begin 258 /* 259 ** 17469, "Tables that reference this object: " 260 */ 261 exec sp_getmessage 17469, @msg output 262 print @msg 263 select distinct 264 object = u.name + "." + o.name 265 into #depend4result 266 from syscolumns c, sysobjects o, sysusers u 267 where @objid IN (c.cdefault, c.domain, c.accessrule) 268 and c.id = o.id 269 and o.uid = u.uid 270 exec sp_autoformat @fulltabname = #depend4result 271 drop table #depend4result 272 select @found_some = 1 273 end 274 275 /* 276 ** Did we find anything in sysdepends? 277 */ 278 if (@found_some = 0) 279 begin 280 /* 281 ** 17464, "Object doesn't reference any object and no objects reference it." 282 */ 283 exec sp_getmessage 17464, @msg output 284 print @msg 285 end 286 287 288 /* 289 ** If object is something other than a table (i.e. view, rule, default, 290 ** trigger etc., return immediately, without further processing for 291 ** column level dependencies. These do not apply to non-table objects, 292 ** and will needlessly add to the processing time for, say, views with 293 ** large number of columns. 294 */ 295 if (select type from sysobjects 296 where id = @objid) not in ('U', 'S') 297 begin 298 return (0) 299 end 300 301 /* 302 ** ========================================================================= 303 ** Start of column level dependency processing. 304 ** ========================================================================= 305 */ 306 307 /* 308 ** Store results in #column_depends as we go 309 */ 310 if ((select object_id("#column_depends")) is NULL) 311 begin 312 create table #column_depends( 313 Type varchar(23) 314 , Property varchar(11) 315 , Dependency varchar(255) -- @Depend_col_length = 255 316 , Column varchar(255) 317 , AlsoSee varchar(100) 318 ) lock allpages 319 end 320 321 declare @Depend_col_length int 322 323 -- Length of Dependency column in output table 324 select @Depend_col_length = 255 325 326 /* 327 ** ========================================================================= 328 ** If no column name was specified, generate dependencies for all columns 329 ** in the table. Output one result at the end of the procedure. 330 */ 331 if @column_name is null 332 begin 333 declare colcur cursor for 334 select name from syscolumns where id = @objid 335 336 open colcur 337 fetch colcur into @column_name 338 339 while (@@sqlstatus != 2) 340 begin 341 -- Change recursion level to skip table-level dependency code 342 exec sp_depends_cols @objname, @column_name 343 , @findTableLevelConstraints = 1 344 345 fetch colcur into @column_name 346 end 347 348 close colcur 349 deallocate cursor colcur 350 351 -- To flag output of column name in end result. 352 select @column_name = NULL 353 354 -- Special case code to pick out check constraints defined at the 355 -- table level. These appear with colid==0 in SYSCONSTRAINTS. Get 356 -- their info, and display them in the summary output report. 357 -- 358 if exists (select colid from sysconstraints 359 where tableid = @objid 360 and colid = 0) -- table level constraints 361 begin 362 insert into #column_depends 363 select "table-level check constraint" 364 , "constraint" 365 , object_name(constrid) 366 + " (Table-level check constraint)" 367 , "(Unknown column name)" 368 , "sp_helpconstraint, alter table drop constraint" 369 from sysconstraints 370 where tableid = @objid 371 and colid = 0 372 end 373 end 374 else 375 begin 376 -- Find column-level dependencies for just this one column. 377 exec sp_depends_cols @objname, @column_name 378 , @findTableLevelConstraints = 0 379 end 380 381 /* 382 ** ========================================================================= 383 ** Did we find anything for the specified column, or for all columns? 384 */ 385 386 -- Only do printing when we are in the first invocation of this sproc, 387 -- and not when we recurse to process each column. 388 -- 389 -- Check and report if no column-level dependencies were found. 390 if ((select count(*) from #column_depends) = 0) 391 begin 392 exec sp_getmessage 17465, @msg output 393 print @msg, @objname 394 395 return (0) 396 end 397 398 -- Report on dependencies for the specified column. 399 if (@column_name is not NULL) 400 begin 401 exec sp_getmessage 17466, @msg output 402 print @msg, @column_name 403 404 exec sp_getmessage 17468, @msg output 405 print @msg 406 407 exec sp_autoformat @fulltabname = #column_depends, 408 @selectlist = "Type, Property, 409 'Object Names or Column Names' = Dependency, 410 'Also see/Use command' = AlsoSee", 411 @orderby = "order by Type" 412 end 413 414 -- Report on dependencies for all columns in the table. 415 else 416 begin 417 print " " 418 exec sp_getmessage 17467, @msg output 419 print @msg 420 421 exec sp_getmessage 17468, @msg output 422 print @msg 423 424 print " " 425 426 exec sp_autoformat @fulltabname = #column_depends, 427 @selectlist = "Column, Type, 428 'Object Names or Column Names' = Dependency", 429 @orderby = "order by Column, Type" 430 end 431 drop table #column_depends 432 set nocount off 433 return (0) 434
exec sp_procxmode 'sp_depends', 'AnyMode' go Grant Execute on sp_depends to public go
DEFECTS | |
![]() | 236 |
![]() | 256 |
![]() | master..sysmessages |
![]() | sybsystemprocs..sysxtypes |
![]() | 165 |
![]() | 195 |
![]() | 242 |
![]() | 270 |
![]() | 407 |
![]() | 426 |
![]() | 390 |
![]() (id, number, depid, depnumber) Intersection: {depid} | 206 |
![]() (id, number, depid, depnumber) Intersection: {depid} | 227 |
![]() | 103 |
![]() | 139 |
![]() | 139 |
![]() | 145 |
![]() | 145 |
![]() | 360 |
![]() | 371 |
![]() | master..spt_values |
![]() | 334 |
![]() | 237 |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 109 |
![]() | 130 |
![]() | 158 |
![]() | 165 |
![]() | 171 |
![]() | 195 |
![]() | 211 |
![]() | 213 |
![]() | 242 |
![]() | 261 |
![]() | 263 |
![]() | 270 |
![]() | 283 |
![]() | 342 |
![]() | 362 |
![]() | 377 |
![]() | 392 |
![]() | 401 |
![]() | 404 |
![]() | 407 |
![]() | 418 |
![]() | 421 |
![]() | 426 |
![]() | 59 |
![]() | 74 |
![]() | 86 |
![]() | 92 |
![]() | 112 |
![]() | 215 |
![]() | 216 |
![]() | 220 |
![]() | 278 |
![]() | 298 |
![]() | 310 |
![]() | 339 |
![]() | 390 |
![]() | 395 |
![]() | 399 |
![]() | 433 |
![]() | 312 |
![]() | 82 |
![]() | 158 |
![]() | 171 |
![]() | 213 |
![]() | 263 |
![]() | 312 |
![]() | 213 |
![]() | 263 |
![]() | 213 |
![]() | 263 |
![]() | 46 |
![]() | 176 |
![]() | 153 |
![]() | 161 |
![]() | 176 |
![]() | 222 |
![]() | 266 |
![]() | 230 |
![]() (id, number, colid) Intersection: {id} | 84 |
![]() (id, number, type, sequence) Intersection: {type, id} | 103 |
![]() (id, number, depid, depnumber) Intersection: {id} | 125 |
![]() (error, dlevel, langid) Intersection: {error, langid} | 137 |
![]() (error, dlevel, langid) Intersection: {error, langid} | 143 |
![]() (id, number, depid, depnumber) Intersection: {id} | 154 |
![]() (id, number, depid, depnumber) Intersection: {id} | 163 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 185 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 188 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 191 |
![]() (id, number, depid, depnumber) Intersection: {id} | 193 |
![]() (id, number, colid) Intersection: {id} | 334 |
![]() | 359 |
![]() | 370 |
![]() | 324 |
![]() | 37 |
![]() | 334 |
![]() | 66 |
![]() | 101 |
![]() | 123 |
![]() | 136 |
![]() | 142 |
![]() | 152 |
![]() | 204 |
![]() | 254 |
![]() | 295 |
![]() | 358 |
![]() | 232 |
![]() | 29 |
![]() | 29 |
![]() | 29 |
![]() | 152 |
![]() | 158 |
![]() | 171 |
![]() | 213 |
![]() | 263 |