sql server ϵͳ±ísysobjects¡¢sysindexes¡¢syscolumnsËð»µÐÞ¸´·½·¨
SQL ServerÊý¾Ý¿â
sql serverÊý¾Ý¿âÖÐÈýÕÅÖØÒªµÄϵͳ±í
sysobjects£ºÔÚÊý¾Ý¿âÄÚ´´½¨µÄÿ¸ö¶ÔÏó£¨Ô¼Êø¡¢Ä¬ÈÏÖµ¡¢ÈÕÖ¾¡¢¹æÔò¡¢´æ´¢¹ý³ÌµÈ£©ÔÚ±íÖÐÕ¼Ò»ÐС£
sysindexes£ºÊý¾Ý¿âÖеÄÿ¸öË÷ÒýºÍ±íÔÚ±íÖи÷Õ¼Ò»ÐС£
syscolumns£ºÃ¿¸ö±íºÍÊÓͼÖеÄÿÁÐÔÚ±íÖÐÕ¼Ò»ÐУ¬´æ´¢¹ý³ÌÖеÄÿ¸ö²ÎÊýÔÚ±íÖÐÒ²Õ¼Ò»ÐС£
ÕâÈýÕűíÓÃID£¨±íID£©×ֶιØÁª¡£ÕâÈýÕÅϵͳ±íÒ»µ©Ë𻵣¬ÓëÖ®¶ÔÓ¦Êý¾Ý¿â¶ÔÏó½«ÎÞ·¨·ÃÎÊ£¬Æä×÷ÓÃÏà
µ±ÓÚDOSÖеġ°Îļþ·ÖÅä±í¡± ¡£
ϵͳ±íË𻵵ÄÖ¢×´
Óà DBCC CHECKDB Я´øÈκβÎÊý¶¼ÎÞ·¨ÐÞ¸´Êý¾Ý¿â£¬Ò²¾ÍÊÇ˵£ºDBCC CHECKDB¶ÔÕâ¸öÕÊÌ׸ù±¾²»Æð×÷ÓÃ
£»
ÎÞ·¨Ö´ÐÐÈçϲÙ×÷£º
select * from sysobjects »òselect * from sysindexes »òselect * from syscolumns £»
ÎÞ·¨ÓÃSQL server DTS»òÆäËûSQL ½Å±¾µ¼¿â¹¤¾ß½øÐе¼¿â£¬µ¼¿âµÄÖÐ;ʧ°Ü£¬±¨¸æ£ºÁ¬½ÓÖжϣ»Î´ÄܶÁ
È¡²¢ãÅËøÒ³£»ÔÚÆóÒµ¹ÜÀíÆ÷»ò²éѯ·ÖÎöÆ÷ÖУ¬²¿·ÖÓû§Êý¾Ý±íÎÞ·¨·ÃÎÊ¡£
´¦Àí·½·¨
´¦ÀíÕâÖÖÊý¾Ý¿â£¬·ÖΪÁ½¸ö´óµÄ²½Ö裺
µÚÒ»²½£º´¦Àí¿ÉÒÔ·ÃÎʵÄÊý¾Ý±í
1)ÕÒ³öÄÄЩ±í²»¿É·ÃÎÊ,¼´£ºÏµÍ³±íÖÐÄÄЩ¼Ç¼Ë𻵣»
2)ÓÃSQL server DTS°ÑÄܹ»·ÃÎʵÄÓû§Êý¾Ý±íµ¼ÈëÒ»¸öеÄDataBase ¡£
ÔÚµ¼¿âʱ£¬²»ÄÜÑ¡ÕÛ(1)Öв»ÄÜ·ÃÎʵÄÊý¾Ý±í¡£
µÚ¶þ²½£º´¦Àí²»¿É·ÃÎʵÄÊý¾Ý±í£º
1) ÕÒ³öϵͳ±íÖдíÎó¼Ç¼µÄID£»
2) ¸ù¾Ý¡°´íÎó¼Ç¼µÄID¡±£¬É¾³ýsysobjects¡¢sysindexes¡¢syscolumns ±í´íÎóµÄ¼Ç¼£»
3) ¸ù¾Ý¡°´íÎó¼Ç¼µÄID¡± £¬Öؽ¨ÏµÍ³±í¼Ç¼£»
4) Öؽ¨Íê±Ï£¬Èç¹û¸Ã±í¿ÉÒÔ·ÃÎÊ£¬ÄÇôÓÃDTSµ¥¶À½«´Ë±íµ¼ÈëеÄDataBase¡£
˵Ã÷£ºÖؽ¨ÏµÍ³±í·½Ê½²»Ò»¶¨»á³É¹¦£¬±ÈÈçÓÉÓÚDISK I/O´íÎó£¬Èç¹û½ö½öÊDZ£´æϵͳ±íµÄ´ÅÅÌÉÈÇø³ö´í
£¬ÄÇôÖؽ¨ÏµÍ³±í·½Ê½¿ÉÒÔÍì»ØÊý¾Ý¡£ Èç¹û±£´æÓû§Êý¾Ý±íµÄ´ÅÅÌÉÈÇø³ö´í£¬ÄÇô¼´Ê¹Öؽ¨ÏµÍ³±íÒ²²»
Äܽâ¾öÎÊÌâ¡£Èç¹ûÖØÒªµÄÓû§Êý¾Ý±íÎÞ·¨µ¼¿â£¬È磺t_Voucher¡¢IcStockbill¡¢ICSaleµÈ£¬ÄÇô¿ÉÒÔÓÃ
Óá°µÚ¶þ²½¡±Öеķ½·¨Ò»ÊÔ¡£
Àý×Ó£º
Ò»sql svrÊý¾Ý¿â£¬ÊµÌåÃûΪ£ºUfdata_36_2018
ÓÃDBCC CHECKDB¼ì²â£¬±¨¸æ£¨ÓÃDBCC CHECKDB ´øÈκβÎÊý¶¼ÊÇÒÔÏÂÌáʾ£©£º
·þÎñÆ÷: ÏûÏ¢ 8966£¬¼¶±ð 16£¬×´Ì¬ 1£¬ÐÐ 1
δÄܶÁÈ¡²¢ãÅËøÒ³ (1:29262)£¨ÓÃãÅËøÀàÐÍ SH£©¡£SYSOBJECTS ʧ°Ü¡£
DBCC Ö´ÐÐÍê±Ï¡£Èç¹û DBCC Êä³öÁË´íÎóÐÅÏ¢£¬ÇëÓëϵͳ¹ÜÀíÔ±ÁªÏµ¡£
Ö´ÐÐselect * from sysobjects£¬±¨¸æÈçÏ£º
·þÎñÆ÷: ÏûÏ¢ 644£¬¼¶±ð 21£¬×´Ì¬ 3£¬ÐÐ 1
δÄÜÔÚË÷ÒýÒ³ (1:29262) ÖÐÕÒµ½ RID ¡®16243a6d19100¡ä µÄË÷ÒýÌõÄ¿£¨Ë÷Òý ID 0£¬Êý¾Ý¿â
¡®Ufdata_36_2018¡ä£©¡£
Á¬½ÓÖжÏ
µ«ÊÇÖ´ÐÐselect * from sysindexes ºÍselect * from syscolumns Õý³£¡£
Õâ˵Ã÷Ö»ÓÐsysobjects±íË𻵣¬¶ø sysindexes ºÍ syscolumns ûÓÐÎÊÌâ¡£
´¦Àí²½Ö裺
µÚÒ»²½£º ´¦Àí¿ÉÒÔ·ÃÎʵÄÊý¾Ý±í
(1.1) ÕÒ³öÄÄЩ±í²»¿É·ÃÎÊ£»
н¨Á¢Ò»¸ösql svrÊý¾Ý¿â£¬Êý¾Ý¿âʵÌåÃûΪUF2018¡£½øÈë²éѯ·ÖÎöÆ÷£¬Ö´ÐÐÈçÏÂSQL£º
¨C****************************************************
use Ufdata_36_2018
DECLARE @TbName VARCHAR(80)
DECLARE FindErrTable SCROLL CURSOR FOR
select name from UF2018.dbo. sysobjects where xtype=''''u'''' order by name
OPEN FindErrTable
FETCH FindErrTable INTO @TbName
WHILE @@FETCH_STATUS<>-1
BEGIN
print @TbName
exec( ''''select top 1 * from '''' + @TbName)
FETCH FindErrTable INTO @TbName
END
PRINT ''''Scan Complate¡''''
CLOSE FindErrTable
DEALLOCATE FindErrTable
¨C****************************************************
Ö´ÐдËSQL¸ø³öµÄ±¨¸æµÄ×îºó¼¸ÐÐΪ£º
¡
T_voucher
·þÎñÆ÷: ÏûÏ¢ 644£¬¼¶±ð 21£¬×´Ì¬ 3£¬ÐÐ 1
δÄÜÔÚË÷ÒýÒ³ (1:29262) ÖÐÕÒµ½ RID ¡®161dd201a100¡ä µÄË÷ÒýÌõÄ¿£¨Ë÷Òý ID 0£¬Êý¾Ý¿â
¡®Ufdata_36_2018¡ä£©¡£
Á¬½ÓÖжÏ
¸ù¾ÝÒÔÉϱ¨¸æ¿ÉÒÔÖªµÀ T_voucher ±íÔÚsysobjects±íÖеĶÔÓ¦¼Ç¼³ö´í£¬Ôì³ÉT_voucher²»ÄÜ·ÃÎÊ¡£ÐÞ
¸ÄÉÏÃæµÄSQL:ÔÚÉùÃ÷ÓαêµÄ¼Ç¼¼¯ÖÐÆÁ±ÎT_voucher ±í¡£¼´£º
¡
DECLARE FindErrTable SCROLL CURSOR FOR
select name from UF2018.dbo. sysobjects where xtype=¡®u¡¯ and name != ¡®t_voucher¡¯
order by name
¡
ÐÞ¸ÄÍê±Ï,¼ÌÐøÖ´ÐдËSQL¡£Èç´Ë·´¸´£¬¾ÍÄܹ»²»¶Ï±¨¸æ³ösysobjectsÖÐÄÇЩ±í²»ÄÜ·ÃÎÊ¡£
(1.2) µ¼ ¿â
ÓÃSQL DTS¹¤¾ß½«Ufdata_36_2018ÖпÉÒÔ·ÃÎʵÄÊý¾Ý±íµ¼ÈëUF2018¡£
µÚ¶þ²½£º´¦Àí²»¿É·ÃÎʵÄÊý¾Ý±í£º
(2.1) ÕÒ³öϵͳ±íÖдíÎó¼Ç¼µÄID
¨C»ñµÃUfdata_36_2018ÖÐT_voucher±íÔÚsysobjectsÖеÄID £º
SELECT id FROM Ufdata_36_2018.dbo.sysobjects WHERE name=¡® t_voucher¡¯
==¡·123
( ˵Ã÷£ºÍ¨³£¼´Ê¹sysobjects±íË𻵣¬²»ÄÜ×ö select * from sysobjects ²éѯ£¬µ«ÊÇ¿ÉÒÔ×ö select
ID,name from sysobjects ²éѯ¡£Èç¹ûselect ID,name from sysobjects ²éѯҲ²»ÄÜÖ´ÐУ¬¿ÉÒÔ¶ÔÕÕ
UF2018ºÍUfdata_36_2018Á½¸öÊý¾Ý¿âÖеÄͬÃû±í: syscolumns¡£¸ù¾ÝUF2018.dbo.syscolumns±íÖÐ
T_voucherËùÕ¼×ֶεĸöÊýÒÔ¼°¸÷¸ö×ֶεÄÃû³Æ£¬ÔÚUfdata_36_2018.dbo.syscolumnsÖÐÕÒ³ö
T_voucherËù¶ÔÓ¦µÄ¼Ç¼£¬ÓÉ´Ë»ñµÃT_voucherÔÚUfdata_36_2018Êý¾Ý¿âµÄϵͳ±íÖÐËù·ÖÅäµÄID¡£)
¨C»ñµÃUF2018ÖÐT_voucher±íÔÚsysobjectsÖеÄID £º
SELECT id FROM UF2018.dbo.sysobjects WHERE name=¡® t_voucher¡¯
==¡·456
(2.2) ɾ³ýUfdata_36_2018ÖÐϵͳ±íÖдíÎó¼Ç¼£º
DELETE Ufdata_36_2018.dbo.sysobjects WHERE id=123
DELETE Ufdata_36_2018.dbo.sysindexes WHERE id=123
DELETE Ufdata_36_2018.dbo.syscolumns WHERE id=123
(2.3) Öؽ¨ÏµÍ³±í¼Ç¼
¨CÖؽ¨Ufdata_36_2018.dbo.sysobjects±íÖÐT_voucher±í¶ÔÓ¦µÄ¼Ç¼£º
INSERT INTO Ufdata_36_2018.dbo.sysobjects
(name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid)
SELECT
¡®t_voucher_b¡¯,123,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcati
d
FROM UF2018.dbo. sysobjects WHERE id=456
¨CÖؽ¨Ufdata_36_2018.dbo.sysindexes±íÖÐt_voucher±í¶ÔÓ¦µÄ¼Ç¼£º
INSERT INTO Ufdata_36_2018.dbo.sysindexes
(id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,rowmodctr,res
erved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,reserved2,FirstIAM,impid,lockfl
ags,pgmodctr,keys,name,statblob)
SELECT
123,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,rowmodctr,res
erved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,reserved2,FirstIAM,impid,lockfl
ags,pgmodctr,keys,name,statblob
FROM UF2018.dbo.sysindexes WHERE id=456
¨CÖؽ¨Ufdata_36_2018.dbo.syscolumns±íÖÐt_voucher±í¶ÔÓ¦µÄ¼Ç¼£º
INSERT INTO Ufdata_36_2018.dbo.syscolumns
(name,id,xtype,typestat,xusertype,length,xprec,xscale,colid,xoffset,bitpos,reserved,colstat
,cdefault,domain,number,colorderby,autoval,offset,collationid,language)
SELECT
name,123,xtype,typestat,xusertype,length,xprec,xscale,colid,xoffset,bitpos,reserved,colstat
,cdefault,domain,number,colORDERBY,autoval,offset,collationid,language
FROM UF2018.dbo.syscolumns WHERE id=456
£¨2.4£©ÓÃDTSµ¥¶À½«t_voucher_b±íµ¼ÈëеÄDataBase
¾¹ýÒÔÉϲÙ×÷£¬Ufdata_36_2018ÖÐt_voucher_b ±íÓëÔt_voucher±í¹²ÓÃͬһID¡£
ÊÔÊÔ¿´¿É·ñÖ´ÐÐSELECT * FROM t_voucher_b ²éѯ -
Èç¹û¿ÉÒÔ£¬ÄÇôt_voucher_b¾ÍÒ»¶¨¼Ì³ÐÔt_voucher±íÖеÄÈ«²¿Êý¾Ý¡£ÔÙÓÃINSERT INTO
UF2018.dbo.T_voucher FROM Ufdata_36_2018.dbo.t_voucher_b
»òDTS ½«t_voucher_bÖеÄÊý¾Ýµ¼ÈëUF2018¡£
Èç¹ûÖ´ÐÐSELECT * FROM t_voucher_b ²éѯÈÔÈ»±¨´í£¬ÕâÕÅ±í³¹µ×ûϷÁË¡£
(2.5) ÆäËû¡±²»¿É·ÃÎʵÄÊý¾Ý±í¡±´¦Àí·½Ê½Í¬ÉÏ£¬Öظ´(2.1)~(2.4) ²½¡£
 
sysindexes ϵͳ±íÐÞ¸´
´¦ÀíÕâÖÖÊý¾Ý¿â£¬·ÖΪÁ½¸ö´óµÄ²½Ö裺
µÚÒ»²½£º´¦Àí¿ÉÒÔ·ÃÎʵÄÊý¾Ý±í
1)ÕÒ³öÄÄЩ±í²»¿É·ÃÎÊ,¼´£ºÏµÍ³±íÖÐÄÄЩ¼Ç¼Ë𻵣»
2)ÓÃSQL server DTS°ÑÄܹ»·ÃÎʵÄÓû§Êý¾Ý±íµ¼ÈëÒ»¸öеÄDataBase ¡£
ÔÚµ¼¿âʱ£¬²»ÄÜÑ¡ÕÛ(1)Öв»ÄÜ·ÃÎʵÄÊý¾Ý±í¡£
µÚ¶þ²½£º´¦Àí²»¿É·ÃÎʵÄÊý¾Ý±í£º
1) ÕÒ³öϵͳ±íÖдíÎó¼Ç¼µÄID£»
2) ¸ù¾Ý¡°´íÎó¼Ç¼µÄID¡±£¬É¾³ýsysobjects¡¢sysindexes¡¢syscolumns ±í´íÎóµÄ¼Ç¼£»
3) ¸ù¾Ý¡°´íÎó¼Ç¼µÄID¡± £¬Öؽ¨ÏµÍ³±í¼Ç¼£»
4) Öؽ¨Íê±Ï£¬Èç¹û¸Ã±í¿ÉÒÔ·ÃÎÊ£¬ÄÇôÓÃDTSµ¥¶À½«´Ë±íµ¼ÈëеÄDataBase¡£
˵Ã÷£ºÖؽ¨ÏµÍ³±í·½Ê½²»Ò»¶¨»á³É¹¦£¬±ÈÈçÓÉÓÚDISK I/O´íÎó£¬Èç¹û½ö½öÊDZ£´æϵͳ±íµÄ´ÅÅÌÉÈÇø³ö´í£¬ÄÇôÖؽ¨ÏµÍ³±í·½Ê½¿ÉÒÔÍì»ØÊý¾Ý¡£ Èç¹û±£´æÓû§Êý¾Ý±íµÄ´ÅÅÌÉÈÇø³ö´í£¬ÄÇô¼´Ê¹Öؽ¨ÏµÍ³±íÒ²²»Äܽâ¾öÎÊÌâ¡£Èç¹ûÖØÒªµÄÓû§Êý¾Ý±íÎÞ·¨µ¼¿â£¬È磺t_Voucher¡¢IcStockbill¡¢ICSaleµÈ£¬ÄÇô¿ÉÒÔÓÃÓá°µÚ¶þ²½¡±Öеķ½·¨Ò»ÊÔ¡£