1、= quotename(table_owner)endelsebeginSELECT full_table_name = quotename(table_owner) +. + quotename(table_name)endend/* Get Object ID */SELECT table_id = object_id(full_table_name)selectTABLE_QUALIFIER = convert(sysname,db_name(),TABLE_OWNER = convert(sysname,user_name(o.uid),TABLE_NAME = convert(sys
2、name,o.name),COLUMN_NAME = convert(sysname,c.name),KEY_SEQ =casewhen c.name = index_col(full_table_name, i.indid, 1) then convert (smallint,1)when c.name = index_col(full_table_name, i.indid, 2) then convert (smallint,2)when c.name = index_col(full_table_name, i.indid, 3) then convert (smallint,3)wh
3、en c.name = index_col(full_table_name, i.indid, 4) then convert (smallint,4)when c.name = index_col(full_table_name, i.indid, 5) then convert (smallint,5)when c.name = index_col(full_table_name, i.indid, 6) then convert (smallint,6)when c.name = index_col(full_table_name, i.indid, 7) then convert (s
4、mallint,7)when c.name = index_col(full_table_name, i.indid, 8) then convert (smallint,8)when c.name = index_col(full_table_name, i.indid, 9) then convert (smallint,9)when c.name = index_col(full_table_name, i.indid, 10) then convert (smallint,10)when c.name = index_col(full_table_name, i.indid, 11)
5、then convert (smallint,11)when c.name = index_col(full_table_name, i.indid, 12) then convert (smallint,12)when c.name = index_col(full_table_name, i.indid, 13) then convert (smallint,13)when c.name = index_col(full_table_name, i.indid, 14) then convert (smallint,14)when c.name = index_col(full_table
6、_name, i.indid, 15) then convert (smallint,15)when c.name = index_col(full_table_name, i.indid, 16) then convert (smallint,16)end,PK_NAME = convert(sysname,i.name)fromsysindexes i, syscolumns c, sysobjects o -, syscolumns c1whereo.id = table_idand o.id = c.idand o.id = i.idand (i.status & 0x800) = 0
7、x800and (c.name = index_col (full_table_name, i.indid, 1) orc.name = index_col (full_table_name, i.indid, 2) orc.name = index_col (full_table_name, i.indid, 3) orc.name = index_col (full_table_name, i.indid, 4) orc.name = index_col (full_table_name, i.indid, 5) orc.name = index_col (full_table_name,
8、 i.indid, 6) orc.name = index_col (full_table_name, i.indid, 7) orc.name = index_col (full_table_name, i.indid, 8) orc.name = index_col (full_table_name, i.indid, 9) orc.name = index_col (full_table_name, i.indid, 10) orc.name = index_col (full_table_name, i.indid, 11) orc.name = index_col (full_tab
9、le_name, i.indid, 12) orc.name = index_col (full_table_name, i.indid, 13) orc.name = index_col (full_table_name, i.indid, 14) orc.name = index_col (full_table_name, i.indid, 15) orc.name = index_col (full_table_name, i.indid, 16)order by 1, 2, 3, 5gogrant execute on sp_pkeys to publicgodump tran mas
10、ter with no_loggoprint creating sp_server_infogocreate proc sp_server_info (attribute_id int = null)asif attribute_id is not nullselect *from master.dbo.spt_server_infowhere attribute_id = attribute_idelseselect *from master.dbo.spt_server_infoorder by attribute_idgogrant execute on sp_server_info t
11、o publicgodump tran master with no_loggoprint creating sp_special_columnsgo/* Procedure for pre-6.0 server */CREATE PROCEDURE sp_special_columns (table_name varchar(32),table_owner varchar(32) = null,table_qualifier varchar(32) = null,col_type char(1) = R,scope char(1) = T,nullable char(1) = U,ODBCV
12、er int = 2)ASDECLARE indid intDECLARE table_id intDECLARE full_table_name varchar(65) /* 2*32+1 */DECLARE scopeout smallintif col_type not in (R,V) or col_type is nullbeginraiserror 20002 Rush_42returnendif scope = Cselect scopeout = 0else if scope = Tselect scopeout = 1elsebeginraiserror 20002 Rush
13、_43returnendif nullable not in (U,O) or nullable is nullbeginraiserror 20002 Rush_44returnendif table_qualifier is not nullbeginif db_name() 0 /* Eliminate Table Row */SELECTSCOPE = scopeout,COLUMN_NAME = convert(varchar(32),INDEX_COL(full_table_name,indid,c2.colid),d.DATA_TYPE,TYPE_NAME = t.name,“P
14、RECISION“ = isnull(d.data_precision, convert(int,c.length),LENGTH = isnull(d.length, convert(int,c.length),SCALE = d.numeric_scale,PSEUDO_COLUMN = convert(smallint,1)FROMsysindexes x,syscolumns c,master.dbo.spt_datatype_info d,systypes t,syscolumns c2 /* Self-join to generate list of index columns a
15、nd */* to extract datatype names */WHEREx.id = table_idAND c.name = INDEX_COL(full_table_name,indid,c2.colid)AND c.id = x.idAND c2.id = x.idAND c2.colid table_qualifierbegin /* If qualifier doesnt match current database */raiserror (15250, -1,-1)returnendendif table_owner is nullbegin /* If unqualif
16、ied table name */SELECT full_table_name = table_nameendelsebegin /* Qualified table name */SELECT full_table_name = table_owner + . + table_nameend/* Get Object ID */SELECT table_id = object_id(full_table_name)if col_type = VBEGIN /* if ROWVER, just run that query */SELECTSCOPE = convert(smallint,NU
17、LL),COLUMN_NAME = convert(varchar(32),c.name),DATA_TYPE = convert(smallint, -2),TYPE_NAME = t.name,“PRECISION“ = convert(int,8),LENGTH = convert(int,8),SCALE = convert(smallint, NULL),PSEUDO_COLUMN = convert(smallint,1)FROMsystypes t, syscolumns cWHEREc.id = table_idAND c.usertype = 80 /* TIMESTAMP
18、*/AND t.usertype = 80 /* TIMESTAMP */RETURNEND/* ROWID, now find the id of the best index for this table */IF nullable = O /* Dont include any indexes that containnullable columns. */SELECT indid = MIN(indid)FROM sysindexes i,syscolumns c,syscolumns c2WHEREi.status&2 = 2 /* If Unique Index */AND c.i
19、d = i.idAND c2.id = c.idAND c2.colid 0 /* Eliminate Table Row */AND c.name = index_col(table_name,i.indid,c2.colid)GROUP BY indid HAVING SUM(c.status&8) = 0ELSE /* Include indexes that are partially nullable. */SELECT indid = MIN(indid)FROM sysindexes iWHEREstatus&2 = 2 /* If Unique Index */AND id =
20、 table_idAND indid 0 /* Eliminate Table Row */SELECTSCOPE = scopeout,COLUMN_NAME = convert(varchar(32),INDEX_COL(full_table_name,indid,c2.colid),d.DATA_TYPE,convert(varchar(32),casewhen (t.usertype 100 or t.usertype in (18,80)then t.nameelse d.TYPE_NAMEend) TYPE_NAME,convert(int,casewhen d.DATA_TYPE
21、 in (6,7) then d.data_precision /* FLOAT/REAL */else isnull(convert(int,c.prec), 2147483647)end) “PRECISION“,convert(int,casewhen d.ss_dtype IN (106, 108, 55, 63) then /* decimal/numeric types */convert(int,c.prec+2)elseisnull(d.length, c.length)end) LENGTH,SCALE = convert(smallint, c.scale),PSEUDO_
22、COLUMN = convert(smallint,1)FROMsysindexes x,syscolumns c,master.dbo.spt_datatype_info d,systypes t,syscolumns c2 /* Self-join to generate list of index columns and */* to extract datatype names */WHEREx.id = table_idAND c.name = INDEX_COL(full_table_name,indid,c2.colid)AND c.id = x.idAND c2.id = x.
23、idAND c2.colid table_qualifierbegin /* If qualifier doesnt match current database */raiserror (15250, -1,-1)returnendendif table_owner is nullbegin /* If unqualified table name */SELECT full_table_name = quotename(table_name)endelsebegin /* Qualified table name */if table_owner = begin /* If empty o
24、wner name */SELECT full_table_name = quotename(table_owner)endelsebeginSELECT full_table_name = quotename(table_owner) +. + quotename(table_name)endend/* Get Object ID */SELECT table_id = object_id(full_table_name)if col_type = VBEGIN /* if ROWVER, just run that query */SELECTSCOPE = convert(smallin
25、t,NULL),COLUMN_NAME = convert(sysname,c.name),DATA_TYPE = convert(smallint, -2),TYPE_NAME = t.name,“PRECISION“ = convert(int,8),LENGTH = convert(int,8),SCALE = convert(smallint, NULL),PSEUDO_COLUMN = convert(smallint,1)FROMsystypes t, syscolumns cWHEREnot (table_id is null)AND c.id = table_idAND t.n
26、ame = timestamp /* TIMESTAMP */AND t.xtype = c.xtypeAND t.xusertype = c.xusertypeRETURNEND/* ROWID, now find the id of the best index for this table */IF nullable = O /* Dont include any indexes that containnullable columns. */SELECT indid = MIN(indid)FROM sysindexes x, syscolumns c, syscolumns c2WH
27、EREnot (table_id is null)AND x.status&2 = 2 /* If Unique Index */AND c.id = x.idAND c2.id = c.idAND c2.colid 0 /* Eliminate Table Row */AND c.name = index_col(table_name,x.indid,c2.colid)GROUP BY indid HAVING SUM(c.status&8) = 0ELSE /* Include indexes that are partially nullable. */SELECT indid = MIN(indid)FROM sysindexes xWHEREnot (table_id is null)AND status&2 = 2 /* If Unique Index */AND id = table_idAND indid 0 /* Eliminate Table Row */
