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

DEFECTS
MEST 4 Empty String will be replaced by Single Space	266
MINU 4 Unique Index with nullable columns sybsystemprocs..sysusermessages	sybsystemprocs..sysusermessages
MTYP 4 Assignment type mismatch constraint_msg: varchar(500) = varchar(1024)	587
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname	652
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname	684
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysreferences.nc2sysreferences (reftabid, indexid, pmrydbname) Intersection: {pmrydbname}	88
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysreferences.csysreferences clustered (tableid, frgndbname) Intersection: {frgndbname}	91
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	249
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	357
QTYP 4 Comparison type mismatch smallint = int	357
TNOU 4 Table with no unique index sybsystemprocs..sysreferences	sybsystemprocs..sysreferences
MGTP 3 Grant to public sybsystemprocs..sp_helpconstraint
MGTP 3 Grant to public sybsystemprocs..syscolumns
MGTP 3 Grant to public sybsystemprocs..syscomments
MGTP 3 Grant to public sybsystemprocs..sysconstraints
MGTP 3 Grant to public sybsystemprocs..sysindexes
MGTP 3 Grant to public sybsystemprocs..sysobjects
MGTP 3 Grant to public sybsystemprocs..sysreferences
MGTP 3 Grant to public sybsystemprocs..sysusermessages
MLCH 3 Char type with length>30 char(60)	628
MLCH 3 Char type with length>30 char(60)	637
MLCH 3 Char type with length>30 char(60)	668
MNER 3 No Error Check should check @@error after insert	207
MNER 3 No Error Check should check @@error after insert	229
MNER 3 No Error Check should check @@error after insert	350
MNER 3 No Error Check should check @@error after insert	542
MNER 3 No Error Check should check @@error after insert	555
MNER 3 No Error Check should check @@error after update	586
MNER 3 No Error Check should check @@error after update	596
MNER 3 No Error Check should check return value of exec	613
MNER 3 No Error Check should check @@error after select into	644
MNER 3 No Error Check should check return value of exec	652
MNER 3 No Error Check should check @@error after select into	676
MNER 3 No Error Check should check return value of exec	684
MNER 3 No Error Check should check return value of exec	694
MNER 3 No Error Check should check return value of exec	697
MNER 3 No Error Check should check return value of exec	699
MNER 3 No Error Check should check return value of exec	701
MNER 3 No Error Check should check return value of exec	703
MNER 3 No Error Check should check return value of exec	706
MNER 3 No Error Check should check return value of exec	708
MNER 3 No Error Check should check return value of exec	710
MNER 3 No Error Check should check return value of exec	712
MNER 3 No Error Check should check return value of exec	714
MUCO 3 Useless Code Useless Brackets	97
MUCO 3 Useless Code Useless Brackets	107
MUCO 3 Useless Code Useless Brackets	130
MUCO 3 Useless Code Useless Brackets	134
MUCO 3 Useless Code Useless Brackets	140
MUCO 3 Useless Code Useless Brackets	144
MUCO 3 Useless Code Useless Brackets	153
MUCO 3 Useless Code Useless Brackets	165
MUCO 3 Useless Code Useless Brackets	174
MUCO 3 Useless Code Useless Brackets	205
MUCO 3 Useless Code Useless Brackets	211
MUCO 3 Useless Code Useless Brackets	227
MUCO 3 Useless Code Useless Brackets	258
MUCO 3 Useless Code Useless Brackets	303
MUCO 3 Useless Code Useless Brackets	327
MUCO 3 Useless Code Useless Brackets	341
MUCO 3 Useless Code Useless Brackets	411
MUCO 3 Useless Code Useless Brackets	455
MUCO 3 Useless Code Useless Brackets	460
MUCO 3 Useless Code Useless Brackets	465
MUCO 3 Useless Code Useless Brackets	471
MUCO 3 Useless Code Useless Brackets	531
MUCO 3 Useless Code Useless Brackets	615
MUCO 3 Useless Code Useless Brackets	624
MUCO 3 Useless Code Useless Brackets	633
MUCO 3 Useless Code Useless Brackets	665
MUCO 3 Useless Code Useless Brackets	691
MUCO 3 Useless Code Useless Brackets	717
MUIN 3 Column created using implicit nullability	192
QCRS 3 Conditional Result Set	86
QCRS 3 Conditional Result Set	626
QCRS 3 Conditional Result Set	635
QCRS 3 Conditional Result Set	667
QCTC 3 Conditional Table Creation	644
QCTC 3 Conditional Table Creation	676
QISO 3 Set isolation level	82
QJWT 3 Join or Sarg Without Index on temp table	590
QJWT 3 Join or Sarg Without Index on temp table	599
QNAJ 3 Not using ANSI Inner Join	216
QNAJ 3 Not using ANSI Inner Join	234
QNAJ 3 Not using ANSI Inner Join	588
QNAJ 3 Not using ANSI Inner Join	598
QNUA 3 Should use Alias: Column text should use alias m	212
QNUA 3 Should use Alias: Column crdate should use alias o	597
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id}	150
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id}	155
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id}	217
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconstraints.csysconstraints clustered (tableid, colid) Intersection: {tableid}	235
QTLO 3 Top-Level OR	382
VNRD 3 Variable is not read @len2	662
VUNU 3 Variable is not used @indid	46
CRDO 2 Read Only Cursor Marker (has for read only clause)	246
CRDO 2 Read Only Cursor Marker (has for read only clause)	373
MRST 2 Result Set Marker	86
MRST 2 Result Set Marker	626
MRST 2 Result Set Marker	635
MRST 2 Result Set Marker	667
MSUB 2 Subquery Marker	153
MSUC 2 Correlated Subquery Marker	87
MSUC 2 Correlated Subquery Marker	90
MTR1 2 Metrics: Comments Ratio Comments: 27%	38
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 57 = 62dec - 7exi + 2	38
MTR3 2 Metrics: Query Complexity Complexity: 323	38
PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, r=sybsystemprocs..sysreferences} 0	87
PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, r=sybsystemprocs..sysreferences} 0	90
PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, c2=sybsystemprocs..syscomments, o=sybsystemprocs..sysobjects} 0	208
PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscomments, c2=sybsystemprocs..sysconstraints, o=sybsystemprocs..sysobjects} 0	230