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

SQL Server 2005Ë÷ÒýË鯬ÕûÀíSQLÓï¾ä

/*******************************************************/
/*         ¹¦ÄÜ:SQL Server 2005Ë÷ÒýË鯬ÕûÀí            */
/*              Âß¼­Ë鯬>=30ÖØ½¨Ë÷Òý,<30ÖØÐÂ×éÖ¯Ë÷Òý   */
/*******************************************************/
/***********SQL Server 2005Ë÷ÒýË鯬ÕûÀí*****************/
/**ʹÓ÷½·¨:½«ÐèÒªÕûÀíË÷ÒýË鯬µÄÊý¾Ý¿âÉèÖÃΪµ±Ç°Êý¾Ý¿â**/
set nocount on
--ʹÓÃÓαêÖØÐÂ×éÖ¯Ö¸¶¨¿âÖеÄË÷Òý,Ïû³ýË÷ÒýË鯬
--R_T²ãÓαêÈ¡³öµ±Ç°Êý¾Ý¿âËùÓбí
declare R_T cursor
for select name from sys.tables
declare @T varchar(50)
open r_t
fetch next from r_t into @t
while @@fetch_status=0
begin
--R_indexÓαêÅжÏÖ¸¶¨±íË÷ÒýË鯬Çé¿ö²¢ÓÅ»¯
declare R_Index cursor
for select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t
  join sys.indexes i on i.object_id=t.object_id
  join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s
   on s.object_id=i.object_id and s.index_id=i.index_id
declare @TName varchar(50),@IName varchar(50),@avg int,@str varchar(500)
open r_index
fetch next from r_index into @TName,@Iname,@avg
while @@fetch_status=0
begin
  if @avg>=30  --Èç¹ûË鯬´óÓÚ30,ÖØ½¨Ë÷Òý
  begin
   set @str='alter index '+rtrim(@Iname)+' on dbo.'+rtrim(@tname)+' rebuild'
  end
  else   --Èç¹ûËéÆ¬Ð¡ÓÚ30,ÖØÐÂ×éÖ¯Ë÷Òý
  begin
   set @STR='alter index '+rtrim(@Iname)+' on dbo.'+rtrim(@tname)+' reorganize'
  end
  print @str
  exec (@str)  --Ö´ÐÐ
  fetch next from r_index into @TName,@Iname,@avg
end
--½áÊør_indexÓαê
close r_index
deallocate r_index
fetch next from r_t into @t
end
--½áÊøR_TÓαê
close r_t
deallocate r_t
set nocount off
/*
--²é¿´Ö¸¶¨±íµÄË÷ÒýÇé¿ö
select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t
join sy


Ïà¹ØÎĵµ£º

³£ÓÃSQLÃæÊÔÌâ £¨Ò»£©

Êý¾Ý¿âÃæÊÔ³£ÓòâÊÔÌâ(SQL Server)
ÌâÄ¿1
¡¡¡¡ÎÊÌâÃèÊö:
¡¡¡¡Îª¹ÜÀí¸ÚλҵÎñÅàѵÐÅÏ¢£¬½¨Á¢3¸ö±í:
¡¡¡¡S (S#,SN,SD,SA) S#,SN,SD,SA ·Ö±ð´ú±íѧºÅ¡¢Ñ§Ô±ÐÕÃû¡¢ËùÊôµ¥Î»¡¢Ñ§Ô±ÄêÁä
¡¡¡¡C (C#,CN ) C#,CN ·Ö±ð´ú±í¿Î³Ì±àºÅ¡¢¿Î³ÌÃû³Æ
¡¡¡¡SC ( S#,C#,G ) S#,C#,G ·Ö±ð´ú±íѧºÅ¡¢ËùÑ¡Ð޵Ŀγ̱àºÅ¡¢Ñ§Ï°³É¼¨
¡¡¡¡1. Ê¹Ó ......

SQLÊý¾Ý¿â»Ö¸´Ä£ÐÍ(ÈÕÖ¾)

----------------------------------------------------------------------
1¡¢SQLÊý¾Ý¿â»Ö¸´Ä£ÐÍ
----------------------------------------------------------------------
1£©ÍêÈ«»Ö¸´Ä£ÐÍ
-----------------
£¨1£©±¸·ÝʱҪ±¸·ÝÊý¾Ý¿âµÄÊý¾ÝÎļþºÍÈÕÖ¾Îļþ
£¨2£©»¹Ô­Ê±Ê¹ÓÃÊý¾Ý¿âµÄ±¸·ÝµÄÊý¾ÝÎļþ¸±±¾ºÍÈ«²¿ÈÕÖ¾ÐÅ ......

SQL SERVERÖÐÒ»Ð©ÌØ±ðµØ·½µÄÌØ±ð½â·¨

SQL code
/*----------------------------------------------------------------
-- Author :feixianxxx(poofly)
-- Date :2010-04-20 20:10:41
-- Version:
-- Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Co ......

SQL Server Management StudioÁ¬½Ó·þÎñÆ÷ʱָ¶¨¶Ë¿ÚºÅ

      ½ñÌìÏëÁ¬½ÓSQL Server·þÎñÆ÷ʱ·¢ÏÖû·¨Á¬½Ó£¬·¢Ïֶ˿ںŲ»ÊÇĬÈϵĶ˿ںţ¬ÐèÒªÖ¸¶¨¶Ë¿ÚºÅ£¬¸Õ¿ªÊ¼ÒÔΪÊÇIPµØÖ·¼ÓðºÅÔٶ˿ںžÍÐÐÁË£¬½á¹û·¢ÏÖûÓã¬googleÒ»°Ñ·¢ÏÖÔ­À´²»ÊÇðºÅ£¬ÊǶººÅ£¬¹þ¡£±ÈÈ磺127.0.0.1,1451 , ¸ñʽ¾ÍÊÇ£º[IP],[Port] ¡£ Íê±Ï¡£ ......

SQLÖеÄCASEʹÓ÷½·¨

Case¾ßÓÐÁ½ÖÖ¸ñʽ¡£¼òµ¥Caseº¯ÊýºÍCaseËÑË÷º¯Êý¡£
--¼òµ¥Caseº¯Êý
CASE sex
         WHEN '1' THEN 'ÄÐ'
         WHEN '2' THEN 'Å®'
ELSE 'ÆäËû' END
--CaseËÑË÷º¯Êý
CASE WHEN sex = '1' THEN 'ÄÐ'
     ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ