Database | Proc | Application | Created | Links |
sybsystemprocs | sp_helpconstraint ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 /* 4 ** 17460, "Object must be in the current database." 5 ** 17461, "Object does not exist in this database." 6 ** 17761, "Object is not a table." 7 ** 18024, "Object does not have any declarative constraints." 8 ** 18243, "Total Number of Referential Constraints: %1!" 9 ** 18449, "Details:" 10 ** 18244, "-- Number of references made by this table: %1!" 11 ** 18245, "-- Number of references to this table: %1!" 12 ** 18246, "-- Number of self references of this table: %1!" 13 ** 18247, "Formula for Calculation:" 14 ** 18450, "Total Number of Referential Consraints" 15 ** 18451, "= Number of references made by this table" 16 ** 18452, "+ Number of references to this table" 17 ** 18453, "- Number of self references within this table" 18 */ 19 20 /* 21 ** Type of declarative constraints returned by this routine are as follows : 22 ** DEFAULT - report the defaults on a column 23 ** PRIMARY KEY - implies clustered index 24 ** UNIQUE KEY - implies non-clustered index; if no clustered 25 ** index exists, this will be clustered 26 ** CHECK CONSTRAINT - check constraint, either column or table 27 ** REFERENCE CONSTRAINT - foreign key/ referential constraint 28 */ 29 30 /* 31 ** IMPORTANT NOTE: 32 ** This stored procedure uses the built-in function object_id() in the 33 ** where clause of a select query. If you intend to change this query 34 ** or use the object_id() or db_id() builtin in this procedure, please read the 35 ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules 36 ** pertaining to object-id's and db-id's outlined there, are followed. 37 */ 38 CREATE PROCEDURE sp_helpconstraint 39 @objname varchar(767) = NULL, /* table to report constraints on */ 40 @propt varchar(10) = "terse" /* print option : 41 ** "detail" - full print 42 ** not supplied or otherwise - terse print 43 */ 44 AS 45 46 DECLARE @indid int /* the index id of an index */ 47 DECLARE @keys varchar(1024) /* string to build up index key in */ 48 DECLARE @inddesc varchar(1065) /* string to build up index desc in */ 49 DECLARE @clust int, @nonclust int /* flag if clust/non-clust index*/ 50 DECLARE @msg varchar(1024) 51 DECLARE @stridx int /* Index of * */ 52 DECLARE @colcnt int 53 DECLARE @indstat int /* status of sysindexes */ 54 DECLARE @indstat2 int /* status2 of sysindexes */ 55 DECLARE @pmytabid int /* flag/id of referencing table */ 56 DECLARE @cnstrname varchar(255) 57 DECLARE @foreign_keys varchar(512) 58 DECLARE @refrncd_keys varchar(512) 59 DECLARE @frgntab varchar(287), @pmrytab varchar(287) 60 DECLARE @matchtype varchar(11) /* Match type of RI */ 61 DECLARE @type char(2) /* Object type */ 62 63 /* Declarations for sysreferences table cursor fetch */ 64 DECLARE @indexid int, @constrid int, @tableid int, @reftabid int, @keycnt int 65 DECLARE @fokey1 int, @fokey2 int, @fokey3 int, @fokey4 int, @fokey5 int 66 DECLARE @fokey6 int, @fokey7 int, @fokey8 int, @fokey9 int, @fokey10 int 67 DECLARE @fokey11 int, @fokey12 int, @fokey13 int, @fokey14 int, @fokey15 int 68 DECLARE @refkey1 int, @refkey2 int, @refkey3 int, @refkey4 int, @refkey5 int 69 DECLARE @refkey6 int, @refkey7 int, @refkey8 int, @refkey9 int, @refkey10 int 70 DECLARE @refkey11 int, @refkey12 int, @refkey13 int, @refkey14 int 71 DECLARE @refkey15 int, @refkey16 int, @fokey16 int, @status int 72 DECLARE @frgndbid int, @pmrydbid int 73 DECLARE @frgndbname varchar(30), @pmrydbname varchar(30) 74 /* Declarations for counts */ 75 DECLARE @total_ref int, @refing int, @ref int, @self_ref int 76 DECLARE @ref_word varchar(30) 77 IF @@trancount = 0 78 BEGIN 79 SET chained off 80 END 81 82 SET transaction isolation level 1 83 84 if @objname is NULL 85 BEGIN 86 select obj.id, obj.name, Num_referential_constraints = 87 (select count(*) from sysreferences 88 where reftabid = obj.id and pmrydbname is NULL) 89 + 90 (select count(*) from sysreferences 91 where tableid = obj.id and frgndbname is NULL 92 and not (reftabid = obj.id and pmrydbname is NULL)) 93 94 from sysobjects obj 95 where (obj.sysstat2 & 3 != 0) and (obj.type = "U") 96 order by Num_referential_constraints desc 97 return (0) 98 END 99 /* 100 ** Check to see that the object names are local to the current database. 101 */ 102 if @objname like "%.%.%" and 103 substring(@objname, 1, charindex(".", @objname) - 1) != db_name() 104 BEGIN 105 /* 17460, "Object must be in the current database." */ 106 raiserror 17460 107 return (1) 108 END 109 110 /* 111 ** Check to see the the table exists and in the same scan, find if 112 ** the table has any check constraints/foreign-key constraints, or 113 ** is being referenced by other tables. 114 */ 115 SELECT @colcnt = 0 116 SELECT @colcnt = id, /* Flag for row in sysobjects found */ 117 @clust = (sysstat & 16), /* Flag for clustered index */ 118 @nonclust = (sysstat & 32), /* Flag for non-clustered index */ 119 @constrid = ckfirst, /* Flag for table check constraint */ 120 @keycnt = (sysstat2 & 4), /* Flag for > 1 table check constr */ 121 @pmytabid = (sysstat2 & 2), /* Flag for foreign key constraint */ 122 @reftabid = (sysstat2 & 1), /* Flag for referenced table constr*/ 123 @type = type /* Object type indicator */ 124 FROM sysobjects 125 WHERE id = object_id(@objname) 126 127 /* 128 ** If table doesn't exist, return. 129 */ 130 IF (@colcnt = 0) 131 BEGIN 132 /* 17461, "Object does not exist in this database." */ 133 raiserror 17461, @objname 134 return (1) 135 END 136 137 /* 138 ** If the object is not a table, return. 139 */ 140 IF (@type not in ('S', 'U')) 141 BEGIN 142 /* 17761, "Object is not a table." */ 143 raiserror 17761, @objname 144 return (1) 145 END 146 147 /* Get number of columns in this table. */ 148 SELECT @colcnt = count(*) 149 FROM syscolumns 150 WHERE id = object_id(@objname) 151 152 /* Check if no columns have any constraints or default */ 153 IF (@colcnt = (SELECT count(*) 154 FROM syscolumns 155 WHERE id = object_id(@objname) 156 AND domain = 0 /* No column check constraint */ 157 AND cdefault = 0)) /* No defaults */ 158 BEGIN 159 SELECT @colcnt = 0 160 END 161 162 /* 163 ** If no constraints on this table, return. 164 */ 165 IF (@clust = 0 /* No clustered index */ 166 AND @nonclust = 0 /* No non-clustered index */ 167 AND @constrid = 0 /* No table check constraints */ 168 AND @pmytabid = 0 /* No foreign key constraints */ 169 AND @reftabid = 0 /* No references to this table */ 170 AND @colcnt = 0) /* No column default or check constraints */ 171 BEGIN 172 /* 18024, "Object does not have any constraints." */ 173 raiserror 18024 174 RETURN (1) 175 END 176 177 /* 178 ** Template for the table we will output. 179 ** id : this may not be useful? 180 ** ermsg: message number assigned by user for this constraint 181 ** name : this will contain name as given by user or system 182 ** colno: number of columns involved in constraint 183 ** type : one of (reference constraint, check constraint) 184 ** msg : one of (user defined, system standard) 185 ** text : actual text of this constraint 186 ** save the info in a temporary table that we'll print out at the end. 187 ** Note that the row length of the table could go beyond the allowed row 188 ** length for a 2K page server if we store the complete message and also 189 ** keep enough space for the constraint description. 190 ** Hence we will store/display only 500 bytes of the constraint message. 191 */ 192 CREATE TABLE #spconstrtab( 193 constraint_id int, 194 constraint_name varchar(255), 195 constraint_colno int, 196 constraint_ermsg int, 197 constraint_type varchar(25), 198 constraint_msg varchar(500) null, 199 constraint_desc varchar(1065) null, 200 constraint_created datetime null) lock allpages 201 202 /* 203 ** Check if the object has any defaults 204 */ 205 IF (@colcnt > 0) 206 BEGIN 207 INSERT INTO #spconstrtab 208 SELECT o.id, o.name, 1, 0, 209 "default value", 210 NULL, 211 case when ((m.text is not NULL) and (m.status & 8 = 8)) 212 then "DEFAULT " + substring(m.text, charindex(o.name, text) + len(o.name) + 5, len(m.text) - (charindex(o.name, text) + len(o.name) + 5)) -- sharable inline default 213 else m.text 214 end, 215 NULL 216 FROM syscolumns c, sysobjects o, syscomments m 217 WHERE c.id = object_id(@objname) 218 AND c.cdefault = o.id 219 AND o.id = m.id 220 END 221 222 /* 223 ** See if the object has any check constraints 224 ** There may be more than one entry in sysconstraints for the object. 225 ** Note : We only handle 1 line of text, need to improve for more lines. 226 */ 227 IF (@colcnt > 0 OR @constrid > 0) 228 BEGIN 229 INSERT INTO #spconstrtab 230 SELECT o.id, o.name, 1, c.error, 231 "check constraint", 232 "standard system error message number : 548", 233 m.text, NULL 234 FROM sysconstraints c, sysobjects o, syscomments m 235 WHERE c.tableid = object_id(@objname) 236 AND c.constrid = o.id 237 AND o.id = m.id 238 AND (o.sysstat & 15 = 7) 239 END 240 241 /* 242 ** Now we search for UNIQUE and PRIMARY KEY (only declarative) constraints 243 */ 244 DECLARE curs_sysindexes 245 CURSOR FOR 246 SELECT keycnt, indid, status, status2 247 FROM sysindexes 248 WHERE id = object_id(@objname) 249 AND indid > 0 250 AND status2 & 2 = 2 251 FOR READ ONLY 252 253 OPEN curs_sysindexes 254 255 FETCH curs_sysindexes 256 INTO @keycnt, @indexid, @indstat, @indstat2 257 258 WHILE (@@sqlstatus = 0) 259 BEGIN 260 /* 261 ** First we'll figure out what the keys are. 262 */ 263 DECLARE @i int 264 DECLARE @thiskey varchar(255) 265 266 SELECT @keys = "", @i = 1 267 268 SET NOCOUNT ON 269 270 WHILE @i <= @keycnt 271 BEGIN 272 SELECT @thiskey = index_col(@objname, @indexid, @i) 273 274 IF @thiskey IS NULL 275 BEGIN 276 GOTO keysdone 277 END 278 279 IF @i > 1 280 BEGIN 281 SELECT @keys = @keys + ", " 282 END 283 284 SELECT @keys = @keys + convert(varchar(30), index_col(@objname, @indexid, @i)) 285 286 /* 287 ** Increment @i so it will check for the next key. 288 */ 289 SELECT @i = @i + 1 290 291 END 292 293 /* 294 ** When we get here we now have all the keys. 295 */ 296 keysdone: 297 SET NOCOUNT OFF 298 299 /* 300 ** Check if we have a PRIMARY KEY constraint or a UNIQUE constraint 301 ** Note that we are only dealing with declarative indexes 302 */ 303 IF (@indstat & 2048 = 2048) 304 BEGIN 305 SELECT @inddesc = "PRIMARY KEY " 306 END 307 ELSE 308 BEGIN 309 SELECT @inddesc = "UNIQUE " 310 END 311 312 /* 313 ** Get the keys involved in the declarative constraint 314 */ 315 SELECT @inddesc = @inddesc + "INDEX (" + @keys + ")" 316 317 /* 318 ** clustered or nonclustered index 319 ** Note that the system by default creates an index 320 */ 321 IF @indexid = 1 322 BEGIN 323 SELECT @inddesc = @inddesc + " : CLUSTERED" 324 END 325 IF @indexid > 1 326 BEGIN 327 IF (@indstat2 & 512 = 512) 328 BEGIN 329 /* clustered index on data only locked table */ 330 SELECT @inddesc = @inddesc + " : CLUSTERED" 331 END 332 ELSE 333 BEGIN 334 SELECT @inddesc = @inddesc + " : NONCLUSTERED" 335 END 336 END 337 338 /* 339 ** Display if this key is referenced by other table 340 */ 341 IF (@indstat2 & 1 = 1) 342 BEGIN 343 SELECT @inddesc = @inddesc + ", FOREIGN REFERENCE" 344 END 345 346 /* 347 ** Now we have the whole description for the index so we'll 348 ** add the goods to the temporary table. 349 */ 350 INSERT INTO #spconstrtab 351 SELECT indid, name, @i - 1, 0, 352 "unique constraint", 353 "standard system error message number : 2601", 354 @inddesc, crdate 355 FROM sysindexes i 356 WHERE id = object_id(@objname) 357 AND indid = @indexid 358 359 FETCH curs_sysindexes 360 INTO @keycnt, @indexid, @indstat, @indstat2 361 362 END 363 364 CLOSE curs_sysindexes 365 366 /* 367 ** Now we obtain the referential dependency information 368 ** The search for matching tableid or reftabid applies to objects in 369 ** this database only (null frgndbname/pmrydbname tells us). 370 */ 371 DECLARE curs_sysreferences 372 CURSOR FOR 373 SELECT constrid, tableid, reftabid, keycnt, status, frgndbid, pmrydbid, 374 fokey1, fokey2, fokey3, fokey4, fokey5, fokey6, 375 fokey7, fokey8, fokey9, fokey10, fokey11, fokey12, 376 fokey13, fokey14, fokey15, fokey16, 377 refkey1, refkey2, refkey3, refkey4, refkey5, refkey6, 378 refkey7, refkey8, refkey9, refkey10, refkey11, refkey12, 379 refkey13, refkey14, refkey15, refkey16, 380 frgndbname, pmrydbname 381 FROM sysreferences 382 WHERE (tableid = object_id(@objname) 383 AND (frgndbname is NULL 384 OR db_id(frgndbname) = db_id())) 385 OR (reftabid = object_id(@objname) 386 AND (pmrydbname is NULL 387 OR db_id(pmrydbname) = db_id())) 388 389 FOR READ ONLY 390 391 OPEN curs_sysreferences 392 393 FETCH curs_sysreferences 394 INTO @constrid, @tableid, @reftabid, @keycnt, @status, @frgndbid, @pmrydbid, 395 @fokey1, @fokey2, @fokey3, @fokey4, @fokey5, @fokey6, 396 @fokey7, @fokey8, @fokey9, @fokey10, @fokey11, @fokey12, 397 @fokey13, @fokey14, @fokey15, @fokey16, 398 @refkey1, @refkey2, @refkey3, @refkey4, @refkey5, @refkey6, 399 @refkey7, @refkey8, @refkey9, @refkey10, @refkey11, @refkey12, 400 @refkey13, @refkey14, @refkey15, @refkey16, 401 @frgndbname, @pmrydbname 402 403 /* 404 ** Initialize the count first. 405 */ 406 SELECT @total_ref = 0 407 SELECT @refing = 0 408 SELECT @ref = 0 409 SELECT @self_ref = 0 410 411 WHILE (@@sqlstatus = 0) 412 BEGIN 413 414 /* 415 ** Set the Database id's from the Database names 416 */ 417 SELECT @pmrydbid = db_id() 418 SELECT @frgndbid = db_id() 419 IF @frgndbname is not NULL 420 SELECT @frgndbid = db_id(@frgndbname) 421 IF @pmrydbname is not NULL 422 SELECT @pmrydbid = db_id(@pmrydbname) 423 424 /* 425 ** Check if either primary or dependent dbids are from this database. 426 ** If both are not, that means we have an invalid entry here. 427 ** Otherwise prefix the database name to the tablename. 428 ** Only look up table names if dbid is non-null (i.e., database exists), 429 ** otherwise object_name will default to current database. 430 */ 431 SELECT @pmrytab = "*" 432 SELECT @frgntab = "*" 433 IF @pmrydbid is not NULL 434 SELECT @pmrytab = object_name(@reftabid, @pmrydbid) 435 IF @frgndbid is not NULL 436 SELECT @frgntab = object_name(@tableid, @frgndbid) 437 438 /* 439 ** Need to enhance this ... 440 */ 441 IF @frgndbid != db_id() 442 BEGIN 443 SELECT @frgntab = @frgndbname + ".." + @frgntab 444 END 445 ELSE 446 BEGIN 447 IF @pmrydbid != db_id() 448 SELECT @pmrytab = @pmrydbname + ".." + @pmrytab 449 END 450 451 /* 452 ** Accumulate the count here. 453 */ 454 455 IF ((@frgndbname is NULL) OR (@pmrydbname is NULL)) 456 BEGIN 457 458 SELECT @ref_word = ") REFERENCES " 459 460 IF (@tableid = object_id(@objname) AND @frgndbname is NULL) 461 BEGIN 462 SELECT @refing = @refing + 1 463 END 464 465 IF (@reftabid = object_id(@objname) AND @pmrydbname is NULL) 466 BEGIN 467 SELECT @ref = @ref + 1 468 END 469 470 /* Is it a self_referencing entry?*/ 471 IF ((@reftabid = @tableid) AND (@frgndbname is NULL) AND 472 (@pmrydbname is NULL)) 473 BEGIN 474 SELECT @self_ref = @self_ref + 1 475 SELECT @ref_word = ") SELF REFERENCES " 476 END 477 END 478 479 SELECT @foreign_keys = isnull(convert(varchar(30), col_name(@tableid, @fokey1, @frgndbid)), "*") + ", " 480 + isnull(convert(varchar(30), col_name(@tableid, @fokey2, @frgndbid)), "*") + ", " 481 + isnull(convert(varchar(30), col_name(@tableid, @fokey3, @frgndbid)), "*") + ", " 482 + isnull(convert(varchar(30), col_name(@tableid, @fokey4, @frgndbid)), "*") + ", " 483 + isnull(convert(varchar(30), col_name(@tableid, @fokey5, @frgndbid)), "*") + ", " 484 + isnull(convert(varchar(30), col_name(@tableid, @fokey6, @frgndbid)), "*") + ", " 485 + isnull(convert(varchar(30), col_name(@tableid, @fokey7, @frgndbid)), "*") + ", " 486 + isnull(convert(varchar(30), col_name(@tableid, @fokey8, @frgndbid)), "*") + ", " 487 + isnull(convert(varchar(30), col_name(@tableid, @fokey9, @frgndbid)), "*") + ", " 488 + isnull(convert(varchar(30), col_name(@tableid, @fokey10, @frgndbid)), "*") + ", " 489 + isnull(convert(varchar(30), col_name(@tableid, @fokey11, @frgndbid)), "*") + ", " 490 + isnull(convert(varchar(30), col_name(@tableid, @fokey12, @frgndbid)), "*") + ", " 491 + isnull(convert(varchar(30), col_name(@tableid, @fokey13, @frgndbid)), "*") + ", " 492 + isnull(convert(varchar(30), col_name(@tableid, @fokey14, @frgndbid)), "*") + ", " 493 + isnull(convert(varchar(30), col_name(@tableid, @fokey15, @frgndbid)), "*") + ", " 494 + isnull(convert(varchar(30), col_name(@tableid, @fokey16, @frgndbid)), "*") 495 496 SELECT @refrncd_keys = isnull(convert(varchar(30), col_name(@reftabid, @refkey1, @pmrydbid)), "*") + ", " 497 + isnull(convert(varchar(30), col_name(@reftabid, @refkey2, @pmrydbid)), "*") + ", " 498 + isnull(convert(varchar(30), col_name(@reftabid, @refkey3, @pmrydbid)), "*") + ", " 499 + isnull(convert(varchar(30), col_name(@reftabid, @refkey4, @pmrydbid)), "*") + ", " 500 + isnull(convert(varchar(30), col_name(@reftabid, @refkey5, @pmrydbid)), "*") + ", " 501 + isnull(convert(varchar(30), col_name(@reftabid, @refkey6, @pmrydbid)), "*") + ", " 502 + isnull(convert(varchar(30), col_name(@reftabid, @refkey7, @pmrydbid)), "*") + ", " 503 + isnull(convert(varchar(30), col_name(@reftabid, @refkey8, @pmrydbid)), "*") + ", " 504 + isnull(convert(varchar(30), col_name(@reftabid, @refkey9, @pmrydbid)), "*") + ", " 505 + isnull(convert(varchar(30), col_name(@reftabid, @refkey10, @pmrydbid)), "*") + ", " 506 + isnull(convert(varchar(30), col_name(@reftabid, @refkey11, @pmrydbid)), "*") + ", " 507 + isnull(convert(varchar(30), col_name(@reftabid, @refkey12, @pmrydbid)), "*") + ", " 508 + isnull(convert(varchar(30), col_name(@reftabid, @refkey13, @pmrydbid)), "*") + ", " 509 + isnull(convert(varchar(30), col_name(@reftabid, @refkey14, @pmrydbid)), "*") + ", " 510 + isnull(convert(varchar(30), col_name(@reftabid, @refkey15, @pmrydbid)), "*") + ", " 511 + isnull(convert(varchar(30), col_name(@reftabid, @refkey16, @pmrydbid)), "*") 512 513 /* trim the list of key-columns */ 514 SELECT @stridx = patindex("%, *%", @foreign_keys) 515 IF @stridx > 0 516 BEGIN 517 SELECT @foreign_keys = substring(@foreign_keys, 1, @stridx - 1) 518 END 519 520 SELECT @stridx = patindex("%, *%", @refrncd_keys) 521 IF @stridx > 0 522 BEGIN 523 SELECT @refrncd_keys = substring(@refrncd_keys, 1, @stridx - 1) 524 END 525 526 SELECT @cnstrname = isnull(object_name(@constrid, @frgndbid), "*") 527 528 /* 529 ** Display if match type of this foreign key constraint is full 530 */ 531 IF (@status & 2 = 2) 532 BEGIN 533 SELECT @matchtype = " MATCH FULL" 534 END 535 ELSE 536 BEGIN 537 SELECT @matchtype = NULL 538 END 539 540 IF db_id() = @frgndbid 541 BEGIN 542 INSERT INTO #spconstrtab 543 SELECT @constrid, @cnstrname, @keycnt, c.error, 544 "referential constraint", 545 "standard system error message number : 547", 546 @frgntab + " FOREIGN KEY (" + @foreign_keys + 547 @ref_word + @pmrytab + "(" + @refrncd_keys + ")" + 548 @matchtype, 549 NULL 550 FROM sysconstraints c 551 WHERE c.constrid = @constrid 552 END 553 ELSE 554 BEGIN 555 INSERT INTO #spconstrtab 556 SELECT @constrid, @cnstrname, @keycnt, 0, 557 "referential constraint", 558 "standard system error message number : 547", 559 @frgntab + " FOREIGN KEY (" + @foreign_keys + 560 @ref_word + @pmrytab + "(" + @refrncd_keys + ")" + 561 @matchtype, 562 NULL 563 END 564 565 FETCH curs_sysreferences 566 INTO @constrid, @tableid, @reftabid, @keycnt, @status, @frgndbid, @pmrydbid, 567 @fokey1, @fokey2, @fokey3, @fokey4, @fokey5, @fokey6, 568 @fokey7, @fokey8, @fokey9, @fokey10, @fokey11, @fokey12, 569 @fokey13, @fokey14, @fokey15, @fokey16, 570 @refkey1, @refkey2, @refkey3, @refkey4, @refkey5, @refkey6, 571 @refkey7, @refkey8, @refkey9, @refkey10, @refkey11, @refkey12, 572 @refkey13, @refkey14, @refkey15, @refkey16, 573 @frgndbname, @pmrydbname 574 575 END 576 577 CLOSE curs_sysreferences 578 579 /* 580 ** Now we setup the error message, if user defined. 581 ** 582 ** constraint_msg and constraing_desc together can not be stored 583 ** for a 2K server, so the description will be truncated to 584 ** 500 bytes. 585 */ 586 UPDATE #spconstrtab 587 SET constraint_msg = u.description 588 FROM sysusermessages u, #spconstrtab c 589 WHERE c.constraint_ermsg >= 20000 590 AND u.error = c.constraint_ermsg 591 592 /* 593 ** Update constraint_created with crdate from sysobjects 594 */ 595 596 UPDATE #spconstrtab 597 SET constraint_created = crdate 598 FROM sysobjects o, #spconstrtab c 599 WHERE c.constraint_id = o.id 600 AND c.constraint_created is null 601 602 /* 603 ** Now prettyprint the results 604 */ 605 606 /* 607 ** First check if #spconstrtab is empty 608 */ 609 610 IF not exists (SELECT * FROM #spconstrtab) 611 BEGIN 612 /* 18024, "Object does not have declarative constraints." */ 613 exec sp_getmessage 18024, @msg output 614 print @msg 615 RETURN (1) 616 END 617 IF @propt NOT LIKE "detail%" 618 BEGIN 619 DECLARE @len1 int, @len2 int, @len3 int 620 621 SELECT @len1 = max(datalength(constraint_name)) FROM #spconstrtab 622 SELECT @len2 = max(datalength(constraint_desc)) FROM #spconstrtab 623 624 IF (@len1 < 15 and @len2 < 60) 625 BEGIN 626 SELECT 627 name = convert(char(15), constraint_name), 628 definition = convert(char(60), constraint_desc), 629 created = convert(char(19), constraint_created, 100) 630 FROM #spconstrtab 631 ORDER BY constraint_type 632 END 633 ELSE IF (@len2 < 60) 634 BEGIN 635 SELECT 636 name = constraint_name, 637 definition = convert(char(60), constraint_desc), 638 created = convert(char(19), constraint_created, 100) 639 FROM #spconstrtab 640 ORDER BY constraint_type 641 END 642 ELSE 643 BEGIN 644 select 645 'name' = constraint_name, 646 'definition' = constraint_desc, 647 'created' = convert(char(19), constraint_created, 100), 648 constraint_type 649 into #spconstrtab_1 650 from #spconstrtab 651 652 exec sp_autoformat @fulltabname = #spconstrtab_1, 653 @selectlist = "name, definition, created", 654 @orderby = "order by constraint_type" 655 drop table #spconstrtab_1 656 END 657 END 658 /* Show all details - pretty printing is not required for this perhaps ? */ 659 ELSE 660 BEGIN 661 SELECT @len1 = max(datalength(constraint_name)) FROM #spconstrtab 662 SELECT @len2 = max(datalength(constraint_msg)) FROM #spconstrtab 663 SELECT @len3 = max(datalength(constraint_desc)) FROM #spconstrtab 664 665 IF (@len1 < 15 and @len3 < 60) 666 BEGIN 667 SELECT name = convert(char(15), constraint_name), 668 definition = convert(char(60), constraint_desc), 669 created = convert(char(19), constraint_created, 100), 670 msg = constraint_msg 671 FROM #spconstrtab 672 ORDER BY constraint_type 673 END 674 ELSE 675 BEGIN 676 select 'name' = constraint_name, 677 'type' = constraint_type, 678 'definition' = constraint_desc, 679 'created' = convert(char(19), constraint_created, 100), 680 'msg' = constraint_msg 681 into #spconstrtab_2 682 from #spconstrtab 683 684 exec sp_autoformat @fulltabname = #spconstrtab_2, 685 @orderby = "order by type" 686 drop table #spconstrtab_2 687 END 688 END 689 690 SELECT @total_ref = @refing + @ref - @self_ref 691 IF (@total_ref > 0) 692 BEGIN 693 PRINT " " 694 EXEC sp_getmessage 18243, @msg out 695 PRINT @msg, @total_ref 696 PRINT " " 697 EXEC sp_getmessage 18449, @msg out 698 PRINT @msg 699 EXEC sp_getmessage 18244, @msg out 700 PRINT @msg, @refing 701 EXEC sp_getmessage 18245, @msg out 702 PRINT @msg, @ref 703 EXEC sp_getmessage 18246, @msg out 704 PRINT @msg, @self_ref 705 PRINT " " 706 EXEC sp_getmessage 18247, @msg out 707 PRINT @msg 708 EXEC sp_getmessage 18450, @msg out 709 PRINT @msg 710 EXEC sp_getmessage 18451, @msg out 711 PRINT @msg 712 EXEC sp_getmessage 18452, @msg out 713 PRINT @msg 714 EXEC sp_getmessage 18453, @msg out 715 PRINT @msg 716 END 717 RETURN (0) 718
exec sp_procxmode 'sp_helpconstraint', 'AnyMode' go Grant Execute on sp_helpconstraint to public go
RESULT SETS | |
sp_helpconstraint_rset_001 | |
sp_helpconstraint_rset_004 | |
sp_helpconstraint_rset_003 | |
sp_helpconstraint_rset_002 |
DEPENDENCIES |
PROCS AND TABLES USED calls proc sybsystemprocs..sp_getmessage ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysreferences ![]() calls proc sybsystemprocs..sp_autoformat ![]() reads table master..syscolumns (1) ![]() read_writes table tempdb..#colinfo_af (1) reads table tempdb..syscolumns (1) ![]() reads table master..systypes (1) ![]() calls proc sybsystemprocs..sp_autoformat ![]() calls proc sybsystemprocs..sp_namecrack ![]() reads table tempdb..systypes (1) ![]() reads table sybsystemprocs..syscomments ![]() writes table tempdb..#spconstrtab_1 (1) reads table sybsystemprocs..sysindexes ![]() writes table tempdb..#spconstrtab_2 (1) reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..sysconstraints ![]() read_writes table tempdb..#spconstrtab (1) reads table sybsystemprocs..syscolumns ![]() reads table sybsystemprocs..sysusermessages ![]() |