Àí½â SQL Server ÖÐϵͳ±íSysobjects
×ÜÓû§±í:select count(*) ×ܱíÊý from sysobjects where xtype='u'
×ÜÓû§±íºÍϵͳ±í:select count(*) ×ܱíÊý from sysobjects where xtype in('u','s')
×ÜÊÓͼÊý:select count(*) ×ÜÊÓͼÊý from sysobjects where xtype='v'
×Ü´æ´¢¹ý³ÌÊý:select count(*) ×Ü´æ´¢¹ý³ÌÊý from sysobjects where xtype='p'
×Ü´¥·¢Æ÷Êý:select count(*) ×Ü´¥·¢Æ÷Êý from sysobjects where xtype='tr'
¹ØÓÚSQL ServerÊý¾Ý¿âµÄÒ»ÇÐÐÅÏ¢¶¼±£´æÔÚËüµÄϵͳ±í¸ñÀï¡£ÎÒ»³ÒÉÄãÊÇ·ñ»¨¹ý±È½Ï¶àµÄʱ¼äÀ´¼ì²éϵͳ±í¸ñ£¬ÒòΪÄã×ÜÊÇæÓÚÓû§±í¸ñ¡£µ«ÊÇ£¬Äã¿ÉÄÜÐèҪż¶û×öÒ»µã²»Í¬Ñ°³£µÄÊ£¬ÀýÈçÊý¾Ý¿âËùÓеĴ¥·¢Æ÷¡£Äã¿ÉÒÔÒ»¸öÒ»¸öµØ¼ì²é±í¸ñ£¬µ«ÊÇÈç¹ûÄãÓÐ500¸ö±í¸ñµÄ»°£¬Õâ¿ÉÄÜ»áÏûºÄÏ൱´óµÄÈ˹¤¡£
Õâ¾ÍÈÃSysobjects±í¸ñÓÐÁËÓÃÎäÖ®µØ¡£ËäÈ»ÎÒ²»½¨ÒéÄã¸üÐÂÕâ¸ö±í¸ñ£¬µ«ÊÇÄ㵱ȻÓÐȨ¶ÔÆä½øÐÐÉó²é¡£
ÔÚ´ó¶àÊýÇé¿öÏ£¬¶ÔÄã×îÓÐÓõÄÁ½¸öÁÐÊÇSysobjects.nameºÍSysobjects.xtype¡£Ç°ÃæÒ»¸öÓÃÀ´Áгö´ý¿¼²ì¶ÔÏóµÄÃû×Ö£¬¶øºóÒ»¸öÓÃÀ´¶¨Òå¶ÔÏóµÄÀàÐÍ
type ÀàÐÍö¾ÙÖµÈçÏ£º
AF = ¾ÛºÏº¯Êý(CLR)
C = ¼ì²éÔ¼Êø
D = ĬÈÏÖµ»ò DEFAULT Ô¼Êø
F = FOREIGN KEY Ô¼Êø
PK = PRIMARY KEY Ô¼Êø£¨ÀàÐÍÊÇ K£©
P = SQL´æ´¢¹ý³Ì
PC = ³ÌÐò¼¯(CLR)´æ´¢¹ý³Ì
FN = SQL±êÁ¿º¯Êý
FS = ³ÌÐò¼¯(CLR)±êÁ¿º¯Êý
FT = ³ÌÐò¼¯(CLR)±íÖµº¯Êý
R = ¹æÔò£¨¾Éʽ£¬¶ÀÁ¢£©
RF = ¸´ÖÆɸѡ´æ´¢¹ý³Ì
L = ÈÕÖ¾
FN = ±êÁ¿º¯Êý
IF = SQLÄÚÁª±íÖµº¯Êý
IT = ÄÚ²¿±í
S = ϵͳ±í
SN = ͬÒå´Ê
SQ = ·þÎñ¶ÓÁÐ
TA = ³ÌÐò¼¯(CLR) DML´¥·¢Æ÷
TR = SQL DML´¥·¢Æ÷
TF = SQL±íÖµº¯Êý
U = ±í£¨Óû§¶¨ÒåÀàÐÍ£©
UQ = UNIQUE Ô¼Êø£¨ÀàÐÍÊÇ K£©
V = ÊÓͼ
X = À©Õ¹´æ´¢¹ý³Ì
ÔÚÅöµ½´¥·¢Æ÷µÄÇéÐÎÏ£¬ÓÃÀ´Ê¶±ð´¥·¢Æ÷ÀàÐ͵ÄÆäËûÈý¸öÁÐÊÇ£ºdeltrig¡¢instrigºÍuptrig¡£
Äã¿ÉÒÔÓÃÏÂÃæµÄÃüÁîÁгö¸ÐÐËȤµÄËùÓжÔÏó£º
SELECT * from sysobjects WHERE xtype = <type of interest>
ÔÚÌØÊâÇé¿öÏ£¬Ò²¾ÍÊÇÔÚ¸¸±í¸ñÓµÓд¥·¢Æ÷µÄÇé¿öÏ£¬Äã¿ÉÄÜÏëÒªÓÃÏÂÃæÕâÑùµÄ´úÂë²éÕÒÊý¾Ý¿â£º
SELECT
Sys2.[name] TableName,
Sys1.[name] TriggerName,
CASE
WHEN Sys1.deltrig > 0 THEN'Delete'
WHEN Sys1.instrig > 0 THEN'Insert'
WHEN Sys1.updtrig > 0 THEN'Update'
END'TriggerType'
from
sysobjects Sys1 JOIN sysobjects Sys2 ON Sys1.parent_obj = Sys2.[id]
WHERE Sys
Ïà¹ØÎĵµ£º
±¾ÎĽÚÑ¡×Ô¡¶Oracle DBAÊּǗ—Êý¾Ý¿âÕï¶Ï°¸ÀýÓëÐÔÄÜÓÅ»¯Êµ¼ù¡·µÚ2Õ“YangtingkunµÄDBA¹¤×÷Êּǔ £¨×÷ÕߣºÑîÍ¢çû£©
V$SQLÊÓͼÏÔʾ½á¹ûÒì³£µÄÕï¶Ï
ÓÐÒ»´ÎÅöµ½Ò»¸öºÜÆæ¹ÖµÄÎÊÌ⣬ÔÚ¼ì²é»á»°ËùÖ´ÐеÄSQLʱ£¬·¢ÏÖV$SQLÊÓͼÖÐSQL_TEXTÁÐÖеÄÊý¾ÝÊDz»Õý³£µÄ¡£
ÓÉÓÚV$SQLÊǶ¯Ì¬ÐÔÄÜÊÓͼ£¬ÀïÃæ±£´æµÄÊǵ± ......
¶ÔÓÚÿһ¸öÊý¾Ý¿âÀ´½²£¬¶¼ÐèÒªÖÁÉÙÒ»¸öÊÂÎñÈÕÖ¾Îļþ¡£ÊÂÎñÈÕÖ¾ÎļþÊÇÕû¸öÊý¾Ý¿âµÄѪҺ£¬Èç¹ûûÓÐÊÂÎñÈÕÖ¾µÄ»°£¬ÄÇô½«ÎÞ·¨½øÐÐÈκβÙ×÷¡£
ÊÂÎñÈÕÖ¾ÓÐʲô¶«Î÷£¿
ÊÂÎñÈÕÖ¾¼Ç¼×ÅÔÚÏà¹ØÊý¾Ý¿âÉϵIJÙ×÷£¬Í¬Ê±»¹´æ´¢Êý¾Ý¿â»Ö¸´(recovery)µÄÏà¹ØÐÅÏ¢¡£
ÊÂÎñÈÕÖ¾ÓëÊý¾Ý¿â»Ö ......
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
¡¡¡¡1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
¡¡¡¡4¡¢ÄÚ´æ²»×ã
¡¡¡¡5¡¢ÍøÂçËÙ¶ÈÂý
¡¡¡¡6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó(¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿) ......
¸ÕÔÚ¿´Ê飬Ìáµ½ÁËsql serverµÄģʽƥÅäÔËË㣬½Ó×ÅÏëµ½ÁËͨÅä·ûµÄתÒåÎÊÌ⣬ÒòΪ̫¾ÃûÓã¬GoogleÁËËÑË÷ÁËһϲÅÏëÆðÀ´£¬Ð´¼¸¾ä»°¼Ç¼Ï¡£ ¹ØÓÚͨÅä·ûµÄתÒ壬sql serverÀï±ßÌṩÁ˹ؼü×ÖescapeÀ´´¦Àí¡£µ«ÊÇescape±¾Éí²»ÊÇʲôתÒå·û£¨¸Õ²ÅÎÒ¾ÍÊÇÔÚÕâÀï¸ã´íÁË£©£¬¶øÊǽ«escapeºóÃæµÄ·ûºÅ¶¨ÒåΪתÒå·û¡£¾Ù¸öÀý£º select ......
----start
SQL(Structured Query Language)£¬Ò²¾ÍÊǽṹ»¯²éѯÓïÑÔ£¬Ëü±»Éè¼ÆÓÃÀ´²Ù×÷¼¯ºÏµÄ£¬ÊǷǹý³Ì»¯µÄÓïÑÔ¡£Ëæ×ÅÓ¦ÓóÌÐòµÄ·¢Õ¹£¬ÒµÎñÂß¼Ô½À´Ô½¸´ÔÓ£¬´«Í³µÄSQLÒѾ²»ÄÜÂú×ãÈËÃǵÄÒªÇó£¬ÓÚÊÇÈËÃǶÔSQL½øÐÐÁËÀ©Õ¹£¬Ê¹Ëü¾ßÓÐÁ˹ý³Ì»¯µÄÂß¼£¬¼´£ºSQL PL¡£SQL PLµÄÈ«³ÆÊÇ SQL Procedural Language£ ......