Edit D:\app\Administrator\product\11.2.0\dbhome_1\sqldeveloper\sqldeveloper\extensions\oracle\dbtools\migration\workbench\plugin\Sybase12Files\get

Edit D:\app\Administrator\product\11.2.0\dbhome_1\sqldeveloper\sqldeveloper\extensions\oracle\dbtools\migration\workbench\plugin\Sybase12Files\get_constraints.sql

/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/*
** 
*/

/*
** Type of declarative constraints returned by this routine are as follows :
**	DEFAULT		     - report the defaults on a column
**	PRIMARY KEY	     - implies clustered index
**	UNIQUE  KEY	     - implies non-clustered index; if no clustered
**			       index exists, this will be clustered
**	CHECK CONSTRAINT     - check constraint, either column or table
**	REFERENCE CONSTRAINT - foreign key/ referential constraint
*/

/*
** IMPORTANT NOTE:
** This batch sql uses the built-in function object_id() in the
** where clause of a select query. If you intend to change this query
** or use the object_id() or db_id() builtin in this procedure, please read the
** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
** pertaining to object-id's and db-id's outlined there, are followed.
*/
set NOCOUNT ON
go
declare my_cursor1 cursor
for select a.name from  sysobjects a, sysusers b where type='U' 
and a.uid=b.uid
go
declare @objname varchar(92) 	/* table to report constraints on */
declare @propt   varchar(10) 	/* print option :
				** "detail" - full print
				** not supplied or otherwise - terse print
				*/

DECLARE @imax2 int, @i2 int
DECLARE @imax1 int, @i1 int
DECLARE @indid int			/* the index id of an index */
DECLARE @keys varchar(1024)		/* string to build up index key in */
DECLARE @inddesc varchar(1065)		/* string to build up index desc in */
DECLARE @clust int, @nonclust int	/* flag if clust/non-clust index*/
DECLARE @msg varchar(1024)
DECLARE @stridx int			/* Index of * */
DECLARE @colcnt int
DECLARE @indstat   int			/* status of sysindexes  */
DECLARE @indstat2  int			/* status2 of sysindexes */
DECLARE @pmytabid  int			/* flag/id of referencing table */
DECLARE @cnstrname varchar(30)
DECLARE @foreign_keys varchar(512)
DECLARE @refrncd_keys varchar(512)
DECLARE @frgntab varchar(30), @pmrytab varchar(30)
DECLARE @matchtype varchar(11)		/* Match type of RI */

/* Declarations for sysreferences table cursor fetch */
DECLARE @indexid int, @constrid int, @tableid int, @reftabid int, @keycnt int
DECLARE @fokey1 int,  @fokey2 int,  @fokey3 int,  @fokey4 int,  @fokey5  int
DECLARE @fokey6 int,  @fokey7 int,  @fokey8 int,  @fokey9 int,  @fokey10 int
DECLARE @fokey11 int, @fokey12 int, @fokey13 int, @fokey14 int, @fokey15 int
DECLARE @refkey1 int, @refkey2 int, @refkey3 int, @refkey4 int, @refkey5  int
DECLARE @refkey6 int, @refkey7 int, @refkey8 int, @refkey9 int, @refkey10 int
DECLARE @refkey11 int, @refkey12 int, @refkey13 int, @refkey14 int
DECLARE @refkey15 int, @refkey16 int, @fokey16 int, @status int
DECLARE @frgndbid int, @pmrydbid int
DECLARE @frgndbname varchar(30), @pmrydbname varchar(30)
/* Declarations for counts */
DECLARE @total_ref int, @refing int, @ref int, @self_ref int
DECLARE @ref_word varchar(30)

select @propt = 'terse'
open my_cursor1
fetch my_cursor1 into @objname
while (@@sqlstatus=0)
begin
IF @@trancount = 0
BEGIN
	SET chained off
END

SET transaction isolation level 1

if @objname is NULL
BEGIN
	select obj.id, obj.name, Num_referential_constraints =
		(select count(*) from sysreferences 
			where reftabid = obj.id and pmrydbname is NULL)
		+
		(select count(*) from sysreferences 
			where tableid = obj.id and frgndbname is NULL 
			and not (reftabid = obj.id and pmrydbname is NULL))
	
	from sysobjects obj
	where ( obj.sysstat2 & 3  != 0 ) and ( obj.type = 'U' )
	order by Num_referential_constraints desc
	goto loop_next 
END
/*
**  Check to see that the object names are local to the current database.
*/
if @objname like '%.%.%' and
	substring(@objname, 1, charindex('.', @objname) - 1) != db_name()
BEGIN
	/* 17460, "Object must be in the current database." */
	/*raiserror 17460*/
	goto loop_next 
END

/*
**  Check to see the the table exists and in the same scan, find if
**  the table has any check constraints/foreign-key constraints, or
**  is being referenced by other tables.
*/
SELECT  @colcnt = 0
SELECT  @colcnt = id,			/* Flag for row in sysobjects found */
	@clust    = (sysstat  & 16),	/* Flag for clustered index */
        @nonclust = (sysstat  & 32),	/* Flag for non-clustered index */
        @constrid = ckfirst,		/* Flag for table check constraint */
	@keycnt   = (sysstat2 & 4),	/* Flag for > 1 table check constr */
	@pmytabid = (sysstat2 & 2),	/* Flag for foreign key constraint */
	@reftabid = (sysstat2 & 1)	/* Flag for referenced table constr*/
FROM sysobjects
WHERE id = object_id(@objname)

/*
**  If table doesn't exist, return.
*/
IF (@colcnt = 0)
BEGIN
	/* 17461, "Object does not exist in this database." */
	/*raiserror 17461*/
	goto loop_next 
END

/*    Get number of columns in this table. */
SELECT @colcnt = count(*)
FROM syscolumns
WHERE id = object_id(@objname)

/*    Check if no columns have any constraints or default */
IF (@colcnt = (SELECT count(*)
	       FROM syscolumns
	       WHERE id = object_id(@objname)
		   AND domain = 0	/* No column check constraint */
		   AND cdefault = 0))	/* No defaults */
BEGIN
	SELECT @colcnt = 0
END

/*
**  If no constraints on this table, return.
*/
IF (@clust = 0			/* No clustered index */
    AND @nonclust = 0		/* No non-clustered index */
    AND @constrid = 0		/* No table check constraints */
    AND @pmytabid = 0		/* No foreign key constraints */
    AND @reftabid = 0		/* No references to this table */
    AND @colcnt   = 0)		/* No column default or check constraints */
BEGIN
	/* 18024, "Object does not have any constraints." */
	/*raiserror 18024*/
	goto loop_next 
END

/*
** Template for the table we will output.
**	    id   : this may not be useful?
**	    ermsg: message number assigned by user for this constraint
**	    name : this will contain name as given by user or system
**	    colno: number of columns involved in constraint
**	    type : one of (reference constraint, check constraint)
**	    msg  : one of (user defined, system standard)
**	    text : actual text of this constraint	    
** save the info in a temporary table that we'll print out at the end.
** Note that the row length of the table could go beyond the allowed row
** length for a 2K page server if we store the complete message and also
** keep enough space for the constraint description. 
** Hence we will store/display only 800 bytes of the constraint message.
*/
CREATE TABLE #spconstrtab (
       constraint_id	int,
       constraint_name	varchar(30),
       constraint_colno int,
       constraint_ermsg int,
       constraint_type	varchar(25),
       constraint_msg	varchar(800) null,
       constraint_desc	varchar(1065) null, 
       constraint_created datetime null) lock allpages

/*
**  Check if the object has any defaults
*/
IF (@colcnt > 0)
BEGIN
INSERT INTO #spconstrtab
       SELECT	o.id, o.name, 1, 0,
		'default value',
		NULL,
		m.text, NULL
       FROM syscolumns c, sysobjects o, syscomments m
       WHERE c.id = object_id(@objname)
	     AND c.cdefault = o.id
	     AND o.id = m.id
END

/*
**  See if the object has any check constraints
**  There may be more than one entry in sysconstraints for the object.
**  Note :  We only handle 1 line of text, need to improve for more lines.
*/
IF (@colcnt > 0 OR @constrid > 0)
BEGIN
INSERT INTO #spconstrtab
       SELECT	o.id, o.name, 1, c.error,
		'check constraint',
		'standard system error message number : 548',
		m.text, NULL
       FROM sysconstraints c, sysobjects o, syscomments m
       WHERE c.tableid = object_id(@objname)
	     AND c.constrid = o.id
	     AND o.id = m.id
	     AND (o.sysstat & 15 = 7)
END

/*
**  Now we search for UNIQUE and PRIMARY KEY (only declarative) constraints
*/
create TABLE #indexes
( RowID numeric(7,0) IDENTITY,
keycnt int,
indid int,
status int, 
status2 int)
INSERT #indexes
SELECT keycnt, indid, status, status2
FROM sysindexes
WHERE  id = object_id(@objname)
	       AND indid > 0
	       AND status2 & 2 = 2

SET @imax2 = @@ROWCOUNT
SET @i2 = 1
WHILE (@i2 <= @imax2)
BEGIN
SELECT @keycnt=keycnt, @indexid=indid, @indstat=status, @indstat2=status2
FROM #indexes
WHERE RowID = @i2

/*
	**  First we'll figure out what the keys are.
	*/
	DECLARE @i int
	DECLARE @thiskey varchar(30)

SELECT @keys = '', @i = 1

SET NOCOUNT ON

WHILE @i <= @keycnt
	BEGIN
		SELECT @thiskey = index_col(@objname, @indexid, @i)

IF @thiskey IS NULL
		BEGIN
			GOTO keysdone
		END

IF @i > 1
		BEGIN
			SELECT @keys = @keys + ', '
		END
			
		SELECT @keys = @keys + index_col(@objname, @indexid, @i)

/*
		**  Increment @i so it will check for the next key.
		*/
		SELECT @i = @i + 1

END

/*
	**  When we get here we now have all the keys.
	*/
	keysdone:
		SET NOCOUNT ON

/*
	** Check if we have a PRIMARY KEY constraint or a UNIQUE constraint
	** Note that we are only dealing with declarative indexes
	*/
	IF (@indstat & 2048 = 2048)
	BEGIN
		SELECT @inddesc = 'PRIMARY KEY '
	END
	ELSE
	BEGIN
		SELECT @inddesc = 'UNIQUE '
	END

/*
	**  Get the keys involved in the declarative constraint
	*/
	SELECT @inddesc = @inddesc + 'INDEX (' + @keys + ')'

/*
	**  clustered or nonclustered index
	**  Note that the system by default creates an index
	*/
	IF @indexid = 1
	BEGIN
		SELECT @inddesc = @inddesc + ' : CLUSTERED'
	END
	IF @indexid > 1
	BEGIN
		IF (@indstat2 & 512 = 512)
		BEGIN
			/* clustered index on data only locked table */
			SELECT @inddesc = @inddesc + ' : CLUSTERED'
		END
		ELSE
		BEGIN
			SELECT @inddesc = @inddesc + ' : NONCLUSTERED'
		END
	END

/*
	**  Display if this key is referenced by other table
	*/
	IF (@indstat2 & 1 = 1)
	BEGIN
		SELECT @inddesc = @inddesc + ', FOREIGN REFERENCE'
	END

/*
	**  Now we have the whole description for the index so we'll
	**  add the goods to the temporary table.
	*/
	INSERT INTO #spconstrtab
	       SELECT indid, name, @i - 1, 0,
		      'unique constraint',
		      'standard system error message number : 2601',
		      @inddesc, crdate
	       FROM sysindexes i
	       WHERE id = object_id(@objname)
		   AND indid = @indexid

SET @i2 = @i2 + 1
END
drop table #indexes

/*
** Now we obtain the referential dependency information
** The search for matching tableid or reftabid applies to objects in
** this database only (null frgndbname/pmrydbname tells us).
*/
create TABLE #references
( RowID numeric(7,0) IDENTITY,
constrid int, tableid int, reftabid int, keycnt int, status int, frgndbid int, pmrydbid int,
       	      fokey1 int, fokey2 int, fokey3 int, fokey4 int, fokey5 int, fokey6 int,
	      fokey7 int, fokey8 int, fokey9 int, fokey10 int, fokey11 int, fokey12 int,
	      fokey13 int, fokey14 int, fokey15 int, fokey16 int,
	      refkey1 int, refkey2 int, refkey3 int, refkey4 int, refkey5 int, refkey6 int,
	      refkey7 int, refkey8 int, refkey9 int, refkey10 int, refkey11 int, refkey12 int,
	      refkey13 int, refkey14 int, refkey15 int, refkey16 int,
	      frgndbname varchar(30) null, pmrydbname varchar(30) null)
INSERT #references
SELECT constrid, tableid, reftabid, keycnt, status, frgndbid, pmrydbid,
       	      fokey1, fokey2, fokey3, fokey4, fokey5, fokey6,
	      fokey7, fokey8, fokey9, fokey10, fokey11, fokey12,
	      fokey13, fokey14, fokey15, fokey16,
	      refkey1, refkey2, refkey3, refkey4, refkey5, refkey6,
	      refkey7, refkey8, refkey9, refkey10, refkey11, refkey12,
	      refkey13, refkey14, refkey15, refkey16,
	      frgndbname, pmrydbname
       FROM   sysreferences
       WHERE  (tableid  = object_id(@objname)
               AND (frgndbname = NULL
                    OR db_id(frgndbname) = db_id()))
          OR  (reftabid = object_id(@objname)
               AND (pmrydbname = NULL
                    OR db_id(pmrydbname) = db_id()))

SET @imax1 = @@ROWCOUNT
SET @i1 = 1
SELECT @total_ref = 0
SELECT @refing = 0
SELECT @ref = 0
SELECT @self_ref = 0
WHILE (@i1 <= @imax1)
BEGIN
SELECT @constrid = constrid, @tableid = tableid, @reftabid = reftabid, @keycnt = keycnt, @status = status, @frgndbid = frgndbid, @pmrydbid = pmrydbid,
     @fokey1 = fokey1, @fokey2 = fokey2, @fokey3 = fokey3, @fokey4 = fokey4, @fokey5 = fokey5, @fokey6 = fokey6,
     @fokey7 = fokey7, @fokey8 = fokey8, @fokey9 = fokey9, @fokey10 = fokey10, @fokey11 = fokey11, @fokey12 = fokey12,
     @fokey13 = fokey13, @fokey14 = fokey14, @fokey15 = fokey15, @fokey16 = fokey16,
     @refkey1 = refkey1, @refkey2 = refkey2, @refkey3 = refkey3, @refkey4 = refkey4, @refkey5 = refkey5, @refkey6 = refkey6,
     @refkey7 = refkey7, @refkey8 = refkey8, @refkey9 = refkey9, @refkey10 = refkey10, @refkey11 = refkey11, @refkey12 = refkey12,
     @refkey13 = refkey13, @refkey14 = refkey14, @refkey15 = refkey15, @refkey16 = refkey16,
     @frgndbname = frgndbname, @pmrydbname = pmrydbname
FROM #references
WHERE RowID = @i1

/*
** Set the Database id's from the Database names
*/
SELECT @pmrydbid = db_id()
SELECT @frgndbid = db_id()
IF @frgndbname != NULL
	SELECT @frgndbid = db_id(@frgndbname)
IF @pmrydbname != NULL
	SELECT @pmrydbid = db_id(@pmrydbname)

/*
**   Check if either primary or dependent dbids are from this database.
**   If both are not, that means we have an invalid entry here.
**   Otherwise prefix the database name to the tablename.
**   Only look up table names if dbid is non-null (i.e., database exists),
**   otherwise object_name will default to current database.
*/
SELECT  @pmrytab = '*'
SELECT  @frgntab = '*'
IF @pmrydbid != NULL
	SELECT  @pmrytab = object_name(@reftabid, @pmrydbid)
IF @frgndbid != NULL
	SELECT  @frgntab = object_name(@tableid,  @frgndbid)

/*
** Need to enhance this ...
*/
IF @frgndbid != db_id()
BEGIN
	SELECT @frgntab = @frgndbname + '..' + @frgntab
END
ELSE
BEGIN
	IF @pmrydbid != db_id()
		SELECT @pmrytab = @pmrydbname + '..' + @pmrytab
END

/* 
** Accumulate the count here.
*/

IF ((@frgndbname is NULL ) OR (@pmrydbname is NULL))
BEGIN

SELECT @ref_word = ') REFERENCES '

IF  (@tableid = object_id(@objname) AND @frgndbname is NULL)
	BEGIN
		SELECT @refing = @refing + 1
	END

IF  (@reftabid = object_id(@objname) AND @pmrydbname is NULL)
	BEGIN
		SELECT @ref = @ref + 1
	END

/* Is it a self_referencing entry?*/
	IF ( (@reftabid = @tableid)  AND (@frgndbname is NULL) AND 
			(@pmrydbname is NULL) )
	BEGIN
		SELECT @self_ref = @self_ref + 1
		SELECT @ref_word = ') SELF REFERENCES '
	END
END

SELECT @foreign_keys = convert(varchar(512),
	  isnull(col_name(@tableid, @fokey1 , @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey2 , @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey3 , @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey4 , @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey5 , @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey6 , @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey7 , @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey8 , @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey9 , @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey10, @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey11, @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey12, @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey13, @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey14, @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey15, @frgndbid), '*') + ', '
	+ isnull(col_name(@tableid, @fokey16, @frgndbid), '*'))

SELECT  @refrncd_keys = convert(varchar(512),
	  isnull(col_name(@reftabid, @refkey1 , @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey2 , @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey3 , @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey4 , @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey5 , @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey6 , @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey7 , @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey8 , @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey9 , @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey10, @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey11, @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey12, @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey13, @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey14, @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey15, @pmrydbid), '*') + ', '
	+ isnull(col_name(@reftabid, @refkey16, @pmrydbid), '*'))

/* trim the list of key-columns */
SELECT @stridx = patindex('%, *%', @foreign_keys) 
IF @stridx > 0
BEGIN
	SELECT @foreign_keys = substring(@foreign_keys, 1, @stridx - 1)
END
 
SELECT @stridx = patindex('%, *%', @refrncd_keys) 
IF @stridx > 0
BEGIN
	SELECT @refrncd_keys = substring(@refrncd_keys, 1, @stridx - 1)
END
 
SELECT @cnstrname = isnull(object_name(@constrid, @frgndbid), '*')

/*
**  Display if match type of this foreign key constraint is full
*/
IF (@status & 2 = 2)
BEGIN
	SELECT @matchtype = ' MATCH FULL'
END
ELSE
BEGIN
	SELECT @matchtype = NULL
END

IF db_id() = @frgndbid
BEGIN
       INSERT INTO #spconstrtab
	      SELECT  @constrid, @cnstrname, @keycnt, c.error,
		      'referential constraint',
		      'standard system error message number : 547',
		      @frgntab + ' FOREIGN KEY (' + @foreign_keys +
		      @ref_word + @pmrytab + '(' + @refrncd_keys + ')' +
		      @matchtype,
		      NULL
	      FROM sysconstraints c
	      WHERE c.constrid = @constrid
END
ELSE
BEGIN
       INSERT INTO #spconstrtab
	      SELECT  @constrid, @cnstrname, @keycnt, 0,
		      'referential constraint',
		      'standard system error message number : 547',
		      @frgntab + ' FOREIGN KEY (' + @foreign_keys +
		      @ref_word + @pmrytab + '(' + @refrncd_keys + ')' +
		      @matchtype,
		      NULL
END

SET @i1 = @i1 + 1
END
drop table #references

/*
**	Now we setup the error message, if user defined.
** 
**      constraint_msg and constraing_desc together can not be stored 
**	for a 2K server, so the description will be truncated to 
**	800 bytes. 
*/
UPDATE #spconstrtab
SET constraint_msg = u.description
FROM sysusermessages u, #spconstrtab c
WHERE c.constraint_ermsg >= 20000
      AND u.error = c.constraint_ermsg

/* 
**  Update constraint_created with crdate from sysobjects 
*/

UPDATE #spconstrtab
SET constraint_created = crdate
FROM sysobjects o, #spconstrtab c
WHERE c.constraint_id = o.id
AND c.constraint_created is null

/*
**	Now prettyprint the results
*/
IF @propt NOT LIKE 'detail%'
BEGIN
	DECLARE @len1 int, @len2 int, @len3 int

SELECT @len1 = max(datalength(constraint_name)) FROM #spconstrtab
	SELECT @len2 = max(datalength(constraint_desc)) FROM #spconstrtab

IF (@len1 < 15 and @len2 < 60)
	BEGIN
		SELECT
            tableid=object_id(@objname),
            col0='<EOC>',
			name = convert(char(15), constraint_name),
            col1='<EOC>',
			definition = convert(char(60), constraint_desc),
            col2='<EOC>',
			/*created = convert(char(19), constraint_created, 100),
            col3='<EOC>',*/
            row1='<EOR>'
		FROM #spconstrtab
		ORDER BY constraint_type
	END
	ELSE IF (@len2 < 60)
	BEGIN
		SELECT
            tableid=object_id(@objname),
            col0='<EOC>',
			name = constraint_name,
            col1='<EOC>',
			definition = convert(char(60), constraint_desc),
            col2='<EOC>',
			/*created = convert(char(19), constraint_created, 100),
            col3='<EOC>'*/
            row1='<EOR>'
		FROM #spconstrtab
		ORDER BY constraint_type
	END
	ELSE
	BEGIN
		SELECT
            tableid=object_id(@objname),
            col0='<EOC>',
			name = constraint_name,
            col1='<EOC>',
			definition = constraint_desc,
            col2='<EOC>',
			/*created = convert(char(19), constraint_created, 100),
            col3='<EOC>'*/
            row1='<EOR>'
		FROM #spconstrtab
		ORDER BY constraint_type
	END
END

drop table #spconstrtab
goto loop_next 
loop_next: 
fetch my_cursor1 into @objname
end
close my_cursor1
go

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de