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 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. Ê¹Ó ......
----------------------------------------------------------------------
1¡¢SQLÊý¾Ý¿â»Ö¸´Ä£ÐÍ
----------------------------------------------------------------------
1£©ÍêÈ«»Ö¸´Ä£ÐÍ
-----------------
£¨1£©±¸·ÝʱҪ±¸·ÝÊý¾Ý¿âµÄÊý¾ÝÎļþºÍÈÕÖ¾Îļþ
£¨2£©»¹ÔʱʹÓÃÊý¾Ý¿âµÄ±¸·ÝµÄÊý¾ÝÎļþ¸±±¾ºÍÈ«²¿ÈÕÖ¾ÐÅ ......
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·þÎñÆ÷ʱ·¢ÏÖû·¨Á¬½Ó£¬·¢Ïֶ˿ںŲ»ÊÇĬÈϵĶ˿ںţ¬ÐèÒªÖ¸¶¨¶Ë¿ÚºÅ£¬¸Õ¿ªÊ¼ÒÔΪÊÇIPµØÖ·¼ÓðºÅÔٶ˿ںžÍÐÐÁË£¬½á¹û·¢ÏÖûÓã¬googleÒ»°Ñ·¢ÏÖÔÀ´²»ÊÇðºÅ£¬ÊǶººÅ£¬¹þ¡£±ÈÈ磺127.0.0.1,1451 , ¸ñʽ¾ÍÊÇ£º[IP],[Port] ¡£ Íê±Ï¡£ ......
Case¾ßÓÐÁ½ÖÖ¸ñʽ¡£¼òµ¥Caseº¯ÊýºÍCaseËÑË÷º¯Êý¡£
--¼òµ¥Caseº¯Êý
CASE sex
WHEN '1' THEN 'ÄÐ'
WHEN '2' THEN 'Å®'
ELSE 'ÆäËû' END
--CaseËÑË÷º¯Êý
CASE WHEN sex = '1' THEN 'ÄÐ'
  ......