Database | Proc | Application | Created | Links |
sybsystemprocs | sp_pkeys | 31 Aug 14 | Defects 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
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) |