SQL ´óÈ«
ÐÄÓêÖ®¼Ò
1.°´ÐÕÊϱʻÅÅÐò:
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.Êý¾Ý¿â¼ÓÃÜ:
select encrypt('ÔʼÃÜÂë')
select pwdencrypt('ÔʼÃÜÂë')
select pwdcompare('ÔʼÃÜÂë','¼ÓÃܺóÃÜÂë') = 1--Ïàͬ£»·ñÔò²»Ïàͬ encrypt('ÔʼÃÜÂë')
select pwdencrypt('ÔʼÃÜÂë')
select pwdcompare('ÔʼÃÜÂë','¼ÓÃܺóÃÜÂë') = 1--Ïàͬ£»·ñÔò²»Ïàͬ
3.È¡»Ø±íÖÐ×Ö¶Î:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='±íA'
set @sql='select '+right(@list,len(@list)-1)+' from ±íA'
exec (@sql)
4.²é¿´Ó²ÅÌ·ÖÇø:
EXEC master..xp_fixeddrives
5.±È½ÏA,B±íÊÇ·ñÏàµÈ:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print 'ÏàµÈ'
else
print '²»ÏàµÈ'
6.ɱµôËùÓеÄʼþ̽²ìÆ÷½ø³Ì:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) from master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL ʼþ̽²éÆ÷')
EXEC sp_msforeach_worker '?'
7.¼Ç¼ËÑË÷:
¿ªÍ·µ½NÌõ¼Ç¼
Select Top N * from ±í
-------------------------------
Nµ½MÌõ¼Ç¼(ÒªÓÐÖ÷Ë÷ÒýID)
Select Top M-N * from ±í Where ID in (Select Top M ID from ±í) Order by ID Desc
----------------------------------
Nµ½½áβ¼Ç¼
Select Top N * from ±í Order by ID Desc
8.ÈçºÎÐÞ¸ÄÊý¾Ý¿âµÄÃû³Æ:
sp_renamedb 'old_name', 'new_name'
9£º»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§±í
select Name from sysobjects where xtype='u' and status>=0
10£º»ñȡijһ¸ö±íµÄËùÓÐ×Ö¶Î
select name from syscolumns where id=object_id('±íÃû')
11£º²é¿´Óëijһ¸ö±íÏà¹ØµÄÊÓͼ¡¢´æ´¢¹ý³Ì¡¢º¯Êý
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%±íÃû%'
12£º²é¿´µ±Ç°Êý¾Ý¿âÖÐËùÓд洢¹ý³Ì
select name as ´æ´¢¹ý³ÌÃû³Æ from sysobjects where xtype='P'
13£º²éѯÓû§´´½¨µÄËùÓÐÊý¾Ý¿â
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..sysdatabase
Ïà¹ØÎĵµ£º
OracleÊý¾Ý¿â
SELECT
*
from
(
SELECT
ROWNUM AS NO_ROW
,row_.*
from
(
SELECT
&nb ......
/*
±êÌ⣺ÆÕͨÐÐÁÐת»»(version 2.0)
×÷Õߣº°®Ð¾õÂÞ.ع»ª
ʱ¼ä£º2008-03-09
µØµã£º¹ã¶«ÉîÛÚ
˵Ã÷£ºÆÕͨÐÐÁÐת»»(version 1.0)½öÕë¶Ôsql server 2000Ìṩ¾²Ì¬ºÍ¶¯Ì¬Ð´·¨£¬version 2.0Ôö¼Ósql server 2005µÄÓйØд·¨¡£
ÎÊÌ⣺¼ÙÉèÓÐÕÅѧÉú³É¼¨±í(tb)ÈçÏÂ:
ÐÕÃû ¿Î³Ì ·ÖÊý
ÕÅÈý ÓïÎÄ 74
ÕÅÈý Êýѧ 83
ÕÅÈý ÎïÀí 93 ......
select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = 'ÏàÓ¦±íÃû')
ÓÃÒÔÉÏsqlÓï¾äÊäÈëÏàÓ¦±íÃû¾Í¿ÉÒԲ鵽±íµÄ×Ö¶ÎÃû£¬¶ÔÓ¦ºÃÊý¾Ý¿â ²éѯÊÇ·ñ´æÔڸñíÓï¾ä
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_cost]') and OBJECTPROPER ......
Ó°ÏìÊý¾Ý¿â»¹ÔËٶȵÄÒòËغÍÓ°ÏìÊý¾Ý¿â±¸·ÝËٶȵÄÒòËØÏàͬ¡£³ý´ËÖ®Í⣬¼ÙÈçÄãʹÓÃSQL Server 2005µÄ»°£¬Ä㻹¿ÉÒÔÆô¶¯ÁíÍâÒ»¸öÓÅ»¯ÈÎÎñÀ´»¹Ôµ±Ç°²»´æÔÚµÄÊý¾Ý¿â£¬ÔËÐл·¾³ÎªWindows XP£¬Windows 2003 Server »ò¸üа汾¡£
Perform Volume Maintenance Tasks
µ±Ä㻹ÔÒ»¸öеÄÍêÕûÊý¾Ý¿âÊÇ£¬SQL Server¶Á±¸·ÝÎļþÍ·£¬È»ºó´ ......
*
sql xml ÈëÃÅ:
--by jinjazz
--http://blog.csdn.net/jinjazz
1¡¢xml: ÄÜÈÏʶԪËØ¡¢ÊôÐÔºÍÖµ
2¡¢xpath: Ñ°Ö·ÓïÑÔ£¬ÀàËÆwind ......