DatabaseProcApplicationCreatedLinks
sybsystemprocssp_pkeys  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*
4     ** note: there is one raiserror message: 18040
5     **
6     ** messages for "sp_pkeys"               18039, 18040
7     **
8     ** 17461, "Object does not exist in this database."
9     ** 18039, "table qualifier must be name of current database."
10    ** 18040, "catalog procedure %1! can not be run in a transaction.", sp_pkeys
11    **
12    */
13    
14    create procedure sp_pkeys
15        @table_name varchar(257),
16        @table_owner varchar(257) = null,
17        @table_qualifier varchar(257) = null
18    as
19        declare @keycnt smallint
20        declare @indexid smallint
21        declare @i int
22        declare @id int
23        declare @uid int
24        select @id = NULL
25    
26    
27        set nocount on
28    
29        if (@@trancount = 0)
30        begin
31            set chained off
32        end
33        else
34        begin
35            /* if inside a transaction */
36            /* catalog procedure sp_pkeys can not be run in a transaction.*/
37            raiserror 18040, "sp_pkeys"
38            return (1)
39        end
40    
41        set transaction isolation level 1
42    
43        if @table_qualifier is not null
44        begin
45            if db_name() != @table_qualifier
46            begin
47                /* if qualifier doesn't match current database */
48                /* "table qualifier must be name of current database"*/
49                raiserror 18039
50                return (1)
51            end
52        end
53    
54        if @table_owner is null
55        begin
56            select @id = id, @uid = uid
57            from sysobjects
58            where name = @table_name
59                and uid = user_id()
60            if (@id is null)
61            begin
62                select @id = id, @uid = uid
63                from sysobjects
64                where name = @table_name
65                    and uid = 1
66            end
67        end
68        else
69        begin
70            select @id = id, @uid = uid
71            from sysobjects
72            where name = @table_name and uid = user_id(@table_owner)
73        end
74    
75        if (@id is null)
76        begin
77            /* 17461, "Object does not exist in this database." */
78            raiserror 17461
79            return (1)
80        end
81    
82        create table #pkeys(
83            table_qualifier varchar(257),
84            table_owner varchar(257),
85            table_name varchar(257),
86            column_name varchar(257),
87            key_seq smallint)
88    
89    
90        /*
91        **  now we search for primary key (only declarative) constraints
92        **  There is only one primary key per table.
93        */
94    
95        select @keycnt = keycnt, @indexid = indid
96        from sysindexes
97        where id = @id
98            and indid > 0 /* make sure it is an index */
99            and status2 & 2 = 2 /* make sure it is a declarative constr */
100           and status & 2048 = 2048 /* make sure it is a primary key */
101   
102       /*
103       **  For non-clustered indexes, keycnt as returned from sysindexes is one
104       **  greater than the actual key count. So we need to reduce it by one to
105       **  get the actual number of keys.
106       */
107   
108       if (@indexid >= 2)
109       begin
110           select @keycnt = @keycnt - 1
111       end
112   
113       select @i = 1
114   
115       while @i <= @keycnt
116       begin
117           insert into #pkeys values
118           (db_name(), user_name(@uid), @table_name,
119               index_col(@table_name, @indexid, @i, @uid), @i)
120           select @i = @i + 1
121       end
122   
123       exec sp_autoformat @fulltabname = #pkeys,
124           @selectlist = "table_qualifier, table_owner, table_name, column_name, key_seq",
125           @orderby = "order by table_qualifier, table_owner, table_name, key_seq"
126       drop table #pkeys
127       return (0)
128   


exec sp_procxmode 'sp_pkeys', 'AnyMode'
go

Grant Execute on sp_pkeys to public
go
DEFECTS
 MTYP 4 Assignment type mismatch key_seq: smallint = int 119
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 123
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 98
 MGTP 3 Grant to public sybsystemprocs..sp_pkeys  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after insert 117
 MNER 3 No Error Check should check return value of exec 123
 MUCO 3 Useless Code Useless Brackets 29
 MUCO 3 Useless Code Useless Brackets 38
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 127
 MUIN 3 Column created using implicit nullability 82
 QISO 3 Set isolation level 41
 MTR1 2 Metrics: Comments Ratio Comments: 32% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 14dec - 4exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 60 14

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
writes table tempdb..#pkeys (1) 
reads table sybsystemprocs..sysindexes  
calls proc sybsystemprocs..sp_autoformat  
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..systypes (1)  
   reads table tempdb..syscolumns (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   read_writes table tempdb..#colinfo_af (1) 
   reads table master..syscolumns (1)