DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropobjectdef  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** Omni only
6     **
7     ** Messages for "sp_dropobjectdef"  18309
8     **
9     ** 18300, "A server name is not permitted in the local object_name."
10    ** 18301, "Database name '%1!' is not the current database."
11    ** 18302, "User '%1!' is not a valid user in the '%2!' database."
12    ** 18309, "You must be the System Administrator (sa) or Database Owner (dbo) to drop a definition for another user's table."
13    ** 18310, "Table has not been defined."
14    ** 18311, "You cannot remove a table definition for a table that has been created. Drop the table first."
15    */
16    create procedure sp_dropobjectdef
17        @tablename varchar(1023) /* table name */
18    as begin
19    
20            declare @uid int,
21                @dso_class smallint,
22                @attrib smallint,
23                @dot tinyint,
24                @db_name varchar(255),
25                @nodb varchar(511),
26                @user_name varchar(255),
27                @tabname varchar(255),
28                @dummy int
29    
30            set nocount on
31    
32            /*
33            ** The table name cannot contain a server name
34            */
35            if @tablename like "%.%.%.%"
36            begin
37                /*
38                ** 18300, "A server name is not permitted in the local object_name."     
39                */
40                raiserror 18300
41                return (1)
42            end
43    
44            /*
45            ** Check that @tablename refers to object in current database,
46            ** and determine correct user id.
47            */
48            if @tablename like "%.%.%"
49            begin
50                select @dot = charindex(".", @tablename)
51                select @db_name = substring(@tablename, 1, @dot - 1)
52    
53                if @db_name != db_name()
54                begin
55                    /*
56                    ** 18301, "Database name '%1!' is not the current database."
57                    */
58                    raiserror 18301, @db_name
59                    return (1)
60                end
61    
62                select @nodb = substring(@tablename, @dot + 1, 511)
63                select @dot = charindex(".", @nodb)
64                select @user_name = substring(@nodb, 1, @dot - 1)
65    
66                if @user_name is NULL
67                begin
68                    select @uid = user_id()
69                end
70                else
71                begin
72                    select @uid = user_id(@user_name)
73                    if @uid is NULL
74                    begin
75                        select @db_name = db_name()
76                        /*
77                        **  18302, "User '%1!' is not a valid user in the '%2!' database."
78                        */
79                        raiserror 18302, @user_name, @db_name
80                        return (1)
81                    end
82                end
83    
84                select @tabname = substring(@nodb, @dot + 1, 255)
85    
86            end
87            else if @tablename like "%.%"
88            begin
89                select @dot = charindex(".", @tablename)
90                select @user_name = substring(@tablename, 1, @dot - 1)
91    
92                if @user_name is NULL
93                begin
94                    select @uid = user_id()
95                end
96                else
97                begin
98                    select @uid = user_id(@user_name)
99                    if @uid is NULL
100                   begin
101                       select @db_name = db_name() /*
102                       **  18302, "User '%1!' is not a valid user in the '%2!' database."
103                       */
104                       raiserror 18302, @user_name, @db_name
105                       return (1)
106                   end
107               end
108   
109               select @tabname = substring(@tablename, @dot + 1, 255)
110   
111           end
112           else
113           begin
114               select @uid = user_id()
115               select @tabname = @tablename
116           end
117   
118           /*
119           ** Only sa or dbo can drop a definition for another user's table.
120           */
121           if @uid != user_id()
122           BEGIN
123               /*
124               ** check if user has sa role, proc_role will
125               ** also do auditing if required. proc_role will also
126               ** print error message if required.
127               */
128               if (charindex("sa_role", show_role()) = 0)
129               BEGIN
130                   select @dummy = proc_role("sa_role")
131                   return (1)
132               END
133           END
134   
135           /*
136           ** Strip out quotes from table name if quoted identifier is
137           ** on
138           */
139           if @tabname like '"%"'
140           begin
141               select @tabname = substring(@tabname, 2, char_length(@tabname) - 2)
142           end
143   
144   
145           /*
146           ** see if the table has already been defined.
147           */
148           select @dso_class = 9, @attrib = 1
149   
150           if not exists (select * from sysattributes
151                   where class = @dso_class and attribute = @attrib and
152                       object_cinfo = @tabname and object_info1 = @uid)
153           begin
154               /*
155               **  18310, "Table has not been defined."
156               */
157               raiserror 18310
158               return (1)
159           end
160   
161           /*
162           ** see if the table has already been created.
163           */
164           if exists (select * from sysobjects where name = @tabname
165                       and uid = @uid and type = 'U')
166           begin
167               /*
168               ** 18311, "You cannot remove .... Drop the table first."
169               */
170               raiserror 18311
171               return (1)
172           end
173   
174           /*
175           ** Ok; delete the row from sysattributes.
176           */
177           delete sysattributes
178           where class = @dso_class and
179               attribute = @attrib and
180               object_cinfo = @tabname and
181               object_info1 = @uid
182   
183           return (0)
184       end
185   
186   


exec sp_procxmode 'sp_dropobjectdef', 'AnyMode'
go

Grant Execute on sp_dropobjectdef to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch @tabname: varchar(255) = varchar(1023) 115
 MGTP 3 Grant to public sybsystemprocs..sp_dropobjectdef  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after delete 177
 MUCO 3 Useless Code Useless Begin-End Pair 18
 MUCO 3 Useless Code Useless Brackets 41
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 183
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object_cinfo, attribute, class}		 151
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object_cinfo, attribute, class}		 178
 VNRD 3 Variable is not read @dummy 130
 MSUB 2 Subquery Marker 150
 MSUB 2 Subquery Marker 164
 MTR1 2 Metrics: Comments Ratio Comments: 39% 16
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 18dec - 8exi + 2 16
 MTR3 2 Metrics: Query Complexity Complexity: 78 16

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..sysattributes  
reads table sybsystemprocs..sysobjects  

CALLERS
called by proc sybsystemprocs..sp_droptabledef