还剩11页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
=quotename@table_ownerendelsebeginSELECT@full_table_name=quotename@table_owner+.+quotename@table_nameendend/*GetObjectID*/SELECT@table_id=object_id@full_table_nameselectTABLE_QUALIFIER=convertsysnamedb_nameTABLE_OWNER=convertsysnameuser_nameo.uidTABLE_NAME=convertsysnameo.nameCOLUMN_NAME=convertsysnamec.nameKEY_SEQ=casewhenc.name=index_col@full_table_namei.indid1thenconvertsmallint1whenc.name=index_col@full_table_namei.indid2thenconvertsmallint2whenc.name=index_col@full_table_namei.indid3thenconvertsmallint3whenc.name=index_col@full_table_namei.indid4thenconvertsmallint4whenc.name=index_col@full_table_namei.indid5thenconvertsmallint5whenc.name=index_col@full_table_namei.indid6thenconvertsmallint6whenc.name=index_col@full_table_namei.indid7thenconvertsmallint7whenc.name=index_col@full_table_namei.indid8thenconvertsmallint8whenc.name=index_col@full_table_namei.indid9thenconvertsmallint9whenc.name=index_col@full_table_namei.indid10thenconvertsmallint10whenc.name=index_col@full_table_namei.indid11thenconvertsmallint11whenc.name=index_col@full_table_namei.indid12thenconvertsmallint12whenc.name=index_col@full_table_namei.indid13thenconvertsmallint13whenc.name=index_col@full_table_namei.indid14thenconvertsmallint14whenc.name=index_col@full_table_namei.indid15thenconvertsmallint15whenc.name=index_col@full_table_namei.indid16thenconvertsmallint16endPK_NAME=convertsysnamei.namefromsysindexesisyscolumnscsysobjectso--syscolumnsc1whereo.id=@table_idando.id=c.idando.id=i.idandi.status0x800=0x800andc.name=index_col@full_table_namei.indid1orc.name=index_col@full_table_namei.indid2orc.name=index_col@full_table_namei.indid3orc.name=index_col@full_table_namei.indid4orc.name=index_col@full_table_namei.indid5orc.name=index_col@full_table_namei.indid6orc.name=index_col@full_table_namei.indid7orc.name=index_col@full_table_namei.indid8orc.name=index_col@full_table_namei.indid9orc.name=index_col@full_table_namei.indid10orc.name=index_col@full_table_namei.indid11orc.name=index_col@full_table_namei.indid12orc.name=index_col@full_table_namei.indid13orc.name=index_col@full_table_namei.indid14orc.name=index_col@full_table_namei.indid15orc.name=index_col@full_table_namei.indid16orderby1235gograntexecuteonsp_pkeystopublicgodumptranmasterwithno_loggoprintcreatingsp_server_infogocreateprocsp_server_info@attribute_idint=nullasif@attribute_idisnotnullselect*frommaster.dbo.spt_server_infowhereattribute_id=@attribute_idelseselect*frommaster.dbo.spt_server_infoorderbyattribute_idgograntexecuteonsp_server_infotopublicgodumptranmasterwithno_loggoprintcreatingsp_special_columnsgo/*Procedureforpre-
6.0server*/CREATEPROCEDUREsp_special_columns@table_namevarchar32@table_ownervarchar32=null@table_qualifiervarchar32=null@col_typechar1=R@scopechar1=T@nullablechar1=U@ODBCVerint=2ASDECLARE@indidintDECLARE@table_idintDECLARE@full_table_namevarchar65/*2*32+1*/DECLARE@scopeoutsmallintif@col_typenotinRVor@col_typeisnullbeginraiserror20002~~Rush_42~~returnendif@scope=Cselect@scopeout=0elseif@scope=Tselect@scopeout=1elsebeginraiserror20002~~Rush_43~~returnendif@nullablenotinUOor@nullableisnullbeginraiserror20002~~Rush_44~~returnendif@table_qualifierisnotnullbeginifdb_name@table_qualifierbegin/*Ifqualifierdoesntmatchcurrentdatabase*/raiserror20001~~Rush_5~~returnendendif@table_ownerisnullbegin/*Ifunqualifiedtablename*/SELECT@full_table_name=@table_nameendelsebegin/*Qualifiedtablename*/SELECT@full_table_name=@table_owner+.+@table_nameend/*GetObjectID*/SELECT@table_id=object_id@full_table_nameif@col_type=VBEGIN/*ifROWVERjustrunthatquery*/SELECTSCOPE=convertsmallintNULLCOLUMN_NAME=convertvarchar32c.nameDATA_TYPE=convertsmallint-3TYPE_NAME=t.namePRECISION=convertint8LENGTH=convertint8SCALE=convertsmallintNULLPSEUDO_COLUMN=convertsmallint1FROMsystypestsyscolumnscWHEREc.id=@table_idANDc.usertype=80/*TIMESTAMP*/ANDt.usertype=80/*TIMESTAMP*/RETURNEND/*ROWIDnowfindtheidofthebestindexforthistable*/IF@nullable=O/*Dontincludeanyindexesthatcontainnullablecolumns.*/SELECT@indid=MINindidFROMsysindexesisyscolumnscsyscolumnsc2WHEREi.status2=2/*IfUniqueIndex*/ANDc.id=i.idANDc
2.id=c.idANDc
2.colidi.keycnt+i.status16/16ANDi.id=@table_idANDindid0/*EliminateTableRow*/ANDc.name=index_col@table_namei.indidc
2.colidGROUPBYindidHAVINGSUMc.status8=0ELSE/*Includeindexesthatarepartiallynullable.*/SELECT@indid=MINindidFROMsysindexesiWHEREstatus2=2/*IfUniqueIndex*/ANDid=@table_idANDindid0/*EliminateTableRow*/SELECTSCOPE=@scopeoutCOLUMN_NAME=convertvarchar32INDEX_COL@full_table_nameindidc
2.colidd.DATA_TYPETYPE_NAME=t.namePRECISION=isnulld.data_precisionconvertintc.lengthLENGTH=isnulld.lengthconvertintc.lengthSCALE=d.numeric_scalePSEUDO_COLUMN=convertsmallint1FROMsysindexesxsyscolumnscmaster.dbo.spt_datatype_infodsystypestsyscolumnsc2/*Self-jointogeneratelistofindexcolumnsand*//*toextractdatatypenames*/WHEREx.id=@table_idANDc.name=INDEX_COL@full_table_name@indidc
2.colidANDc.id=x.idANDc
2.id=x.idANDc
2.colidkeycnt+x.status16/16ANDx.indid=@indidANDt.type=d.ss_dtypeANDc.length=d.fixlenANDc.usertype=t.usertypegoifcharindex
6.00@@version=0andcharindex
6.50@@version=0andcharindex
7.00@@version=0andcharindex
8.00@@version=0beginprintprintprintWarning:printyouareinstallingthestoredproceduresprintonapre
6.0SQLServer.printIgnorethefollowingerrors.endelsedropprocsp_special_columnsgo/*Procedurefor
6.0and
6.50servers*/CREATEPROCEDUREsp_special_columns@table_namevarchar32@table_ownervarchar32=null@table_qualifiervarchar32=null@col_typechar1=R@scopechar1=T@nullablechar1=U@ODBCVerint=2ASDECLARE@indidintDECLARE@table_idintDECLARE@full_table_namevarchar65/*2*32+1*/DECLARE@scopeoutsmallintif@col_typenotinRVor@col_typeisnullbeginraiserror15251-1-1col_typeRorVreturnendif@scope=Cselect@scopeout=0elseif@scope=Tselect@scopeout=1elsebeginraiserror15251-1-1scopeCorTreturnendif@nullablenotinUOor@nullableisnullbeginraiserror15251-1-1nullableUorOreturnendif@table_qualifierisnotnullbeginifdb_name@table_qualifierbegin/*Ifqualifierdoesntmatchcurrentdatabase*/raiserror15250-1-1returnendendif@table_ownerisnullbegin/*Ifunqualifiedtablename*/SELECT@full_table_name=@table_nameendelsebegin/*Qualifiedtablename*/SELECT@full_table_name=@table_owner+.+@table_nameend/*GetObjectID*/SELECT@table_id=object_id@full_table_nameif@col_type=VBEGIN/*ifROWVERjustrunthatquery*/SELECTSCOPE=convertsmallintNULLCOLUMN_NAME=convertvarchar32c.nameDATA_TYPE=convertsmallint-2TYPE_NAME=t.namePRECISION=convertint8LENGTH=convertint8SCALE=convertsmallintNULLPSEUDO_COLUMN=convertsmallint1FROMsystypestsyscolumnscWHEREc.id=@table_idANDc.usertype=80/*TIMESTAMP*/ANDt.usertype=80/*TIMESTAMP*/RETURNEND/*ROWIDnowfindtheidofthebestindexforthistable*/IF@nullable=O/*Dontincludeanyindexesthatcontainnullablecolumns.*/SELECT@indid=MINindidFROMsysindexesisyscolumnscsyscolumnsc2WHEREi.status2=2/*IfUniqueIndex*/ANDc.id=i.idANDc
2.id=c.idANDc
2.colidi.keycnt+i.status16/16ANDi.id=@table_idANDindid0/*EliminateTableRow*/ANDc.name=index_col@table_namei.indidc
2.colidGROUPBYindidHAVINGSUMc.status8=0ELSE/*Includeindexesthatarepartiallynullable.*/SELECT@indid=MINindidFROMsysindexesiWHEREstatus2=2/*IfUniqueIndex*/ANDid=@table_idANDindid0/*EliminateTableRow*/SELECTSCOPE=@scopeoutCOLUMN_NAME=convertvarchar32INDEX_COL@full_table_nameindidc
2.colidd.DATA_TYPEconvertvarchar32casewhent.usertype100ort.usertypein1880thent.nameelsed.TYPE_NAMEendTYPE_NAMEconvertintcasewhend.DATA_TYPEin67thend.data_precision/*FLOAT/REAL*/elseisnullconvertintc.prec2147483647endPRECISIONconvertintcasewhend.ss_dtypeIN1061085563then/*decimal/numerictypes*/convertintc.prec+2elseisnulld.lengthc.lengthendLENGTHSCALE=convertsmallintc.scalePSEUDO_COLUMN=convertsmallint1FROMsysindexesxsyscolumnscmaster.dbo.spt_datatype_infodsystypestsyscolumnsc2/*Self-jointogeneratelistofindexcolumnsand*//*toextractdatatypenames*/WHEREx.id=@table_idANDc.name=INDEX_COL@full_table_name@indidc
2.colidANDc.id=x.idANDc
2.id=x.idANDc
2.colidx.keycnt+x.status16/16ANDx.indid=@indidANDt.type=d.ss_dtypeANDd.ODBCVerisnullord.ODBCVer=@ODBCVerANDisnulld.AUTO_INCREMENT0=c.status128/128ANDc.usertype=t.usertypegoifcharindex
7.00@@version=0andcharindex
8.00@@version=0beginprintprintprintWarning:printyouareinstallingthestoredproceduresprintonapre
7.0SQLServer.printIgnorethefollowingerrors.endelsedropprocsp_special_columnsgo/*Procedurefor
7.0servers*/CREATEPROCEDUREsp_special_columns@table_namesysname@table_ownersysname=null@table_qualifiersysname=null@col_typechar1=R@scopechar1=T@nullablechar1=U@ODBCVerint=2ASDECLARE@indidintDECLARE@table_idintDECLARE@full_table_namenvarchar257DECLARE@scopeoutsmallintif@col_typenotinRVor@col_typeisnullbeginraiserror15251-1-1col_typeRorVreturnendif@scope=Cselect@scopeout=0elseif@scope=Tselect@scopeout=1elsebeginraiserror15251-1-1scopeCorTreturnendif@nullablenotinUOor@nullableisnullbeginraiserror15251-1-1nullableUorOreturnendif@table_qualifierisnotnullbeginifdb_name@table_qualifierbegin/*Ifqualifierdoesntmatchcurrentdatabase*/raiserror15250-1-1returnendendif@table_ownerisnullbegin/*Ifunqualifiedtablename*/SELECT@full_table_name=quotename@table_nameendelsebegin/*Qualifiedtablename*/if@table_owner=begin/*Ifemptyownername*/SELECT@full_table_name=quotename@table_ownerendelsebeginSELECT@full_table_name=quotename@table_owner+.+quotename@table_nameendend/*GetObjectID*/SELECT@table_id=object_id@full_table_nameif@col_type=VBEGIN/*ifROWVERjustrunthatquery*/SELECTSCOPE=convertsmallintNULLCOLUMN_NAME=convertsysnamec.nameDATA_TYPE=convertsmallint-2TYPE_NAME=t.namePRECISION=convertint8LENGTH=convertint8SCALE=convertsmallintNULLPSEUDO_COLUMN=convertsmallint1FROMsystypestsyscolumnscWHEREnot@table_idisnullANDc.id=@table_idANDt.name=timestamp/*TIMESTAMP*/ANDt.xtype=c.xtypeANDt.xusertype=c.xusertypeRETURNEND/*ROWIDnowfindtheidofthebestindexforthistable*/IF@nullable=O/*Dontincludeanyindexesthatcontainnullablecolumns.*/SELECT@indid=MINindidFROMsysindexesxsyscolumnscsyscolumnsc2WHEREnot@table_idisnullANDx.status2=2/*IfUniqueIndex*/ANDc.id=x.idANDc
2.id=c.idANDc
2.colidx.keycnt+x.status16/16ANDx.id=@table_idANDindid0/*EliminateTableRow*/ANDc.name=index_col@table_namex.indidc
2.colidGROUPBYindidHAVINGSUMc.status8=0ELSE/*Includeindexesthatarepartiallynullable.*/SELECT@indid=MINindidFROMsysindexesxWHEREnot@table_idisnullANDstatus2=2/*IfUniqueIndex*/ANDid=@table_idANDindid0/*EliminateTableRow*/SELECTSCOPE=@scopeoutCOLUMN_NAME=convertsysnameINDEX_COL@full_table_nameindidc
2.colidd.DATA_TYPEconvertsysnamecasewhent.xusertype255thent.nameelsed.TYPE_NAMEendTYPE_NAMEconvertintcasewhend.DATA_TYPEin67thend.data_precision/*FLOAT/REAL*/elseOdbcPrecc.xtypec.lengthc.xprecendPRECISIONconvertintcasewhentype_named.ss_dtypeINnumericdecimalthen/*decimal/numerictypes*/OdbcPrecc.xtypec.lengthc.xprec+2elseisnulld.lengthc.lengthendLENGTHSCALE=convertsmallintOdbcScalec.xtypec.xscalePSEUDO_COLUMN=convertsmallint1FROMsysindexesxsyscolumnscmaster.dbo.spt_datatype_infodsystypestsyscolumnsc2/*Self-jointogeneratelistofindexcolumnsand*//*toextractdatatypenames*/WHEREnot@table_idisnullANDx.id=@table_idANDc.name=INDEX_COL@full_table_name@indidc
2.colidANDc.id=x.idANDc
2.id=x.idANDc
2.colidx.keycnt+x.status16/16ANDx.indid=@indidANDt.xtype=d.ss_dtypeANDd.ODBCVerisnullord.ODBCVer=@ODBCVerANDisnulld.AUTO_INCREMENT0=isnullColumnPropertyc.idc.nameIsIdentity0ANDc.xusertype=t.xusertypegoifcharindex
8.00@@version=0beginprintprintprintWarning:printyouareinstallingthestoredproceduresprintonapre
8.0SQLServer.printIgnorethefollowingerrors.endelsedropprocsp_special_columnsgo/*Procedurefor
8.0andlaterservers*/CREATEPROCEDUREsp_special_columns@table_namesysname@table_ownersysname=null@table_qualifiersysname=null@col_typechar1=R@scopechar1=T@nullablechar1=U@ODBCVerint=2ASDECLARE@indidintDECLARE@table_idintDECLARE@full_table_namenvarchar257DECLARE@scopeoutsmallintif@col_typenotinRVor@col_typeisnullbeginraiserror15251-1-1col_typeRorVreturnendif@scope=Cselect@scopeout=0elseif@scope=Tselect@scopeout=1elsebeginraiserror15251-1-1scopeCorTreturnendif@nullablenotinUOor@nullableisnullbeginraiserror15251-1-1nullableUorOreturnendif@table_qualifierisnotnullbeginifdb_name@table_qualifierbegin/*Ifqualifierdoesntmatchcurrentdatabase*/raiserror15250-1-1returnendendif@table_ownerisnullbegin/*Ifunqualifiedtablename*/SELECT@full_table_name=quotename@table_nameendelsebegin/*Qualifiedtablename*/if@table_owner=begin/*Ifemptyownername*/SELECT@full_table_name=quotename@table_ownerendelsebeginSELECT@full_table_name=quotename@table_owner+.+quotename@table_nameendend/*GetObjectID*/SELECT@table_id=object_id@full_table_nameif@col_type=VBEGIN/*ifROWVERjustrunthatquery*/SELECTSCOPE=convertsmallintNULLCOLUMN_NAME=convertsysnamec.nameDATA_TYPE=convertsmallint-2TYPE_NAME=t.namePRECISION=convertint8LENGTH=convertint8SCALE=convertsmallintNULLPSEUDO_COLUMN=convertsmallint1FROMsystypestsyscolumnscWHEREnot@table_idisnullANDc.id=@table_idANDt.name=timestamp/*TIMESTAMP*/ANDt.xtype=c.xtypeANDt.xusertype=c.xusertype。