Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

MSSQLÊý¾Ý¿â²Ù×÷ʹÓÃÓï¾ä


1.°´ÐÕÊϱʻ­ÅÅÐò: 
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 
2.·ÖÒ³SQLÓï¾ä 
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from ±íÃû As tab) As t where rownum between ÆðʼλÖà And ½áÊøÎ»Öà
3.»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§±í 
select * from sysobjects where xtype='U' and category=0 
4.»ñȡijһ¸ö±íµÄËùÓÐ×ֶΠ
select name from syscolumns where id=object_id('±íÃû') 
5.²é¿´Óëijһ¸ö±íÏà¹ØµÄÊÓͼ¡¢´æ´¢¹ý³Ì¡¢º¯Êý 
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%±íÃû%' 
6.²é¿´µ±Ç°Êý¾Ý¿âÖÐËùÓд洢¹ý³Ì 
select name as ´æ´¢¹ý³ÌÃû³Æ from sysobjects where xtype='P' 
7.²éѯÓû§´´½¨µÄËùÓÐÊý¾Ý¿â 
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa') 
»òÕß 
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01 
8.²éѯijһ¸ö±íµÄ×ֶκÍÊý¾ÝÀàÐÍ 
select column_name,data_type from information_schema.columns 
where table_name = '±íÃû' 
9.ʹÓÃÊÂÎñ 
ÔÚʹÓÃһЩ¶ÔÊý¾Ý¿â±íµÄÁÙʱµÄSQLÓï¾ä²Ù×÷ʱ£¬¿ÉÒÔ²ÉÓÃSQL SERVERÊÂÎñ´¦Àí£¬·ÀÖ¹¶ÔÊý¾Ý²Ù×÷ºó·¢ÏÖÎó²Ù×÷ÎÊÌâ 
¿ªÊ¼ÊÂÎñ 
Begin tran 
  Insert Into TableName Values(…) 
SQLÓï¾ä²Ù×÷²»Õý³££¬Ôò»Ø¹öÊÂÎñ¡£ 
»Ø¹öÊÂÎñ 
Rollback tran 
SQLÓï¾ä²Ù×÷Õý³££¬ÔòÌá½»ÊÂÎñ£¬Êý¾ÝÌá½»ÖÁÊý¾Ý¿â¡£ 
Ìá½»ÊÂÎñ 
Commit tran 
10. °´È«ÎÄÆ¥Å䷽ʽ²éѯ 
×Ö¶ÎÃû LIKE N'%[^a-zA-Z0-9]China[^a-zA-Z0-9]%' 
OR ×Ö¶ÎÃû LIKE N'%[^a-zA-Z0-9]China' 
OR ×Ö¶ÎÃû LIKE N'China[^a-zA-Z0-9]%' 
OR ×Ö¶ÎÃû LIKE N'China 
11£®¼ÆËãÖ´ÐÐSQLÓï¾ä²éѯʱ¼ä 
declare @d datetime 
set @d=getdate() 
select * from SYS_ColumnProperties select [Óï¾äÖ´Ðл¨·Ñʱ¼ä(ºÁÃë)]=datediff(ms,@d,getdate()) 
12¡¢ËµÃ÷£º¼¸¸ö¸ß¼¶²éѯÔËËã´Ê 
A£º UNION ÔËËã·û 
UNION ÔËËã·ûͨ¹ý×éºÏÆäËûÁ½¸ö½á¹û±í£¨ÀýÈç TABLE1 ºÍ TA


Ïà¹ØÎĵµ£º

MSSQL´æ´¢¹ý³ÌʵÀý

Create proc RegisterUser
(
@usrName varchar(30)
,@usrPasswd varchar(30)
,@age int
,@PhoneNum varchar(20)
,@Address varchar(50)
)
as
begin
--ÏÔʾ¶¨Òå²¢¿ªÊ¼Ò»¸öÊÂÎñ
begin tran
insert into user
(
userName
,userPasswd
)
values
(
@usrName
,@usrPassw ......

mssqlÀïsp_MSforeachtableºÍsp_MSforeachdbµÄÓ÷¨

´Ómssql6.5¿ªÊ¼£¬Î¢ÈíÌṩÁËÁ½¸ö²»¹«¿ª£¬·Ç³£ÓÐÓõÄϵͳ´æ´¢¹ý³Ìsp_MSforeachtableºÍsp_MSforeachdb£¬ÓÃÓÚ±éÀúij¸öÊý¾Ý¿âµÄÿ¸ö±íºÍ±éÀúDBMS¹ÜÀíϵÄÿ¸öÊý¾Ý¿â¡£
ÎÒÃÇÔÚmasterÊý¾Ý¿âÀïÖ´ÐÐÏÂÃæµÄÓï¾ä¿ÉÒÔ¿´µ½Á½¸öprocÏêϸµÄ´úÂë
use master
exec sp_helptext sp_MSforeachtable
exec sp_helptext sp_Msforeachdb
sp_M ......

½²½âMSSQLÊý¾Ý¿âÖÐSQLËø»úÖÆºÍÊÂÎñ¸ôÀë¼¶±ð

Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²Ï ......

mssql Öн«ºº×Öת»»ÎªÆ´ÒôÊ××Öĸ·½·¨


1.
--½«ºº×Öת»»ÎªÆ´ÒôÊ××Öĸ
CREATE   function   GetAllPY(@str   nvarchar(4000))
returns   nvarchar(4000)
--WITH   ENCRYPTION
as
begin
declare   @intLen int
declare   @strRet nvarchar(4000)
declare   @temp   nvarchar(100)
set   @intLen &nb ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ