sql server ÖеÄһЩʵÓõÄsqlÓï¾ä
¼ò½é
ÔÚÕâƪÎÄÕÂÖУ¬ÎÒÁоÙһЩsqlÓï¾äÀ´½éÉÜÊý¾Ý¿â£¬Êý¾Ý±í£¬ÊÓͼµÈµÈ¡£µ±ÎÒÃÇÔÚʹÓòéѯ²éѯ²Ù×÷ʱÕâЩsqlÓï¾ä¶¼ÊǷdz£ÓÐÓõġ£ËäÈ»ÔÚsql server¶ÔÏóä¯ÀÀÆ÷ÖÐÎÒÃÇÒ²¿ÉÒÔ»ñµÃÕâЩÓï¾ä£¬µ«ÊÇÈç¹ûÎÒÃÇдÕâЩÓï¾äʱÎÒÃÇ¿ÉÒÔ½«Ëü×Ô¶¨Òå¡£Õâ¾ÍÒâζ×ÅÎÒÃÇ¿ÉÒÔ¸øÓè×Ô¼ºµÄÐèÇóÀ´¹ýÂ˽á¹û¡£
sqlÓï¾äÁбí
ÈçºÎÁоÙsql serverµ±Ç°Á¬½ÓµÄ¿ÉÓÃÊý¾Ý¿â
Method 1 : SP_DATABASES
Method 2 : SELECT name from SYS.DATABASES
Method 3 : SELECT name from SYS.MASTER_FILES
Method 4 : SELECT * from SYS.MASTER_FILES -- Type=0 for .mdf and type=1 for .ldf
SP_DATABASESÊÇÒ»¸ö¿ÉÒÔÁоÙÊý¾Ý¿â¼°Æä´óСµÄ´æ´¢¹ý³Ì
sys.databasesÓï¾äÖпÉÒÔÁоÙÊý¾Ý¿âÃû³Æ£¬´´½¨ÈÕÆÚ£¬ÐÞ¸ÄÈÕÆÚ£¬ÒѾÊý¾Ý¿âidºÍÆäËûһЩÐÅÏ¢¡£
SYS.MASTER_FILESÓï¾ä¿ÉÒÔ²éѯÊý¾ÝµÄÏêϸÇé¿ö£¬±ÈÈçÊý¾Ý¿âid£¬´óС£¬ÎïÀí´æ´¢Â·¾¶ÒÔ¼°ÁоÙÊý¾Ý¿âmdfºÍldf.
ÈçºÎÁоÙÊý¾Ý¿âÖеÄÊý¾Ý±í
ÒÔϵÄsqlÓï¾ä¶¼¿ÉÒÔÁбísql serverÊý¾Ý¿âÖеÄÓû§±í.
Method 1 : SELECT name from SYS.OBJECTS WHERE type='U'
Method 2 : SELECT NAME from SYSOBJECTS WHERE xtype='U'
Method 3 : SELECT name from SYS.TABLES
Method 4 : SELECT name from SYS.ALL_OBJECTS WHERE type='U'
Method 5 : SELECT table_name from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
Method 6 : SP_TABLES
ÈçºÎÁоÙÊý¾Ý¿âÖеĴ洢¹ý³Ì
Method 1 : SELECT name from SYS.OBJECTS WHERE type='P'
Method 2 : SELECT name from SYS.PROCEDURES
Method 3 : SELECT name from SYS.ALL_OBJECTS WHERE type='P'
Method 4 : SELECT NAME from SYSOBJECTS WHERE xtype='P'
Method 5 : SELECT Routine_name from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'
SYS.OBJECTSÊý¾Ý±í°üº¬ÁËÈ«²¿µÄ´æ´¢¹ý³Ì£¬Êý¾Ý±í£¬´¥·¢Æ÷£¬ÊÓͼµÈµÄÐÅÏ¢£¬ÕâÀïʹÓÃtype=’p'À´²éѯ´æ´¢¹ý³Ì.
Information_schema.routinesÔÚsql server 7.0ÊÇÒ»¸öÊý¾ÝÊÓͼ£¬ÔÚÆäºóµÄ°æ±¾ÖÐÒѾ±ä³É´æ´¢¹ý³ÌרÓеıí.
ÈçºÎÁоÙÊý¾Ý¿âÖеÄÊÓͼ
Method 1 : SELECT name from SYS.OBJECTS WHERE type='V'
Method 2 : SELECT name from SYS.ALL_OBJECTS WHERE type='V' 
Ïà¹ØÎĵµ£º
select * from pet;
insert into pet values('Liujingwei','Liuchao','cat','f','1984-04-18',null);
UPDATE pet set birth='1989-08-31' WHERE name='Slim';
select * from pet WHERE birth>'1998-1-1';
SELECT * from pet WHEREselect * from pet;
insert into pet values('Liujingwei','Liuchao','cat','f','198 ......
Select CONVERT(varchar(100), GETDATE(), 23)£»
·µ»ØÐÎʽ£º2008-11-29
Select CONVERT(varchar(100), GETDATE(), 102)
·µ»ØÐÎʽ£º2008.11.29
Select CONVERT(varchar(100), GETDATE(), 101)
·µ»ØÐÎʽ£º11/29/2008
¸ü¶àÏêÇéÇë²Î¼ûÈçÏÂÁÐ±í£º
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 ......
Ò»¡¢±í½á¹¹²éѯ
SELECT TOP (100) PERCENT a.name AS zdm,COLUMNPROPERTY(a.id, a.name, 'IsIdentity') AS bs ,
CASE WHEN EXISTS (SELECT 1 from dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc. ......
Ò»¡¢¼òµ¥²éѯ
¡¡¡¡ ¼òµ¥µÄTransact-SQL²éѯֻ°üÀ¨Ñ¡ÔñÁÐ±í¡¢from×Ó¾äºÍWHERE×Ӿ䡣
ËüÃÇ·Ö±ð˵Ã÷Ëù²éѯÁС¢²éѯµÄ
±í»òÊÓͼ¡¢ÒÔ¼°ËÑË÷Ìõ¼þµÈ¡£
ÀýÈ磬ÏÂÃæµÄÓï¾ä²éѯtesttable±íÖÐÐÕÃûΪ“ÕÅÈý”µÄnickname×ֶκÍemail×ֶΡ£
SELECT nickname,email
from testtable WHERE name='ÕÅÈý'
(Ò»)Ñ¡ÔñÁбí
¡ ......
CREATE Table <±íÃû>
£¨[<ÁÐÃû1>] ÀàÐÍ (³¤¶È) [ȱʡֵ][Áм¶Ô¼Êø]
[£¬<ÁÐÃû2> Êý¾ÝÀàÐÍ[ȱʡֵ][Áм¶Ô¼Êø]]….
[£¬UNIQUE£¨ÁÐÃû[£¬ÁÐÃû]….£©]
[£¬PRIMARY KEY£¨ÁÐÃû[£¬ÁÐÃû]…£©]
&n ......