【正文】 lect name from syscolumns where id=object_id(39。)2說明：列示type、vender、pcs字段，以type字段排列，case可以方便地實現(xiàn)多重選擇，類似select 中的case。A39。C39。B39。39。select count(*)as Total from [39。] where 39。select count(*)as Total from [39。]39。set strSQL = 39。 + tblName + 39。+ strWhere收縮數(shù)據(jù)庫重建索引 DBCC REINDEX DBCC INDEXDEFRAG收縮數(shù)據(jù)和日志 DBCC SHRINKDB DBCC SHRINKFILE壓縮數(shù)據(jù)庫dbcc shrinkdatabase(dbname)轉(zhuǎn)移數(shù)據(jù)庫給新用戶以已存在用戶權(quán)限exec sp_change_users_login 39。,39。,39。 go檢查備份集RESTORE VERIFYONLY from disk=39。修復(fù)數(shù)據(jù)庫ALTER DATABASE [dvbbs] SET SINGLE_USER GO DBCC CHECKDB(39。,repair_allow_data_loss)WITH TABLOCK GO ALTER DATABASE [dvbbs] SET MULTI_USER GO日志清除 SET NOCOUNT ON DECLARE LogicalFileName sysname, MaxMinutes INT, NewSize INTUSE tablename要操作的數(shù)據(jù)庫名SELECT LogicalFileName = 39。,日志文件名 MaxMinutes = 10,Limit on time allowed to wrap log.NewSize = 1你想設(shè)定的日志文件的大小(M)Setup / initialize DECLARE OriginalSize int SELECT OriginalSize = size FROM sysfiles WHERE name = LogicalFileName SELECT 39。 + db_name()+ 39。 + CONVERT(VARCHAR(30),OriginalSize)+ 39。 + CONVERT(VARCHAR(30),(OriginalSize*8/1024))+ 39。 FROM sysfiles WHERE name = LogicalFileName CREATE TABLE DummyTrans(DummyColumn char(8000)not null)DECLARE Counter INT, StartTime DATETIME, TruncLog VARCHAR(255)SELECT StartTime = GETDATE(), TruncLog = 39。 + db_name()+ 39。 DBCC SHRINKFILE(LogicalFileName, NewSize)EXEC(TruncLog)Wrap the log if MaxMinutes DATEDIFF(mi, StartTime, GETDATE())time has not expiredAND OriginalSize =(SELECT size FROM sysfiles WHERE name = LogicalFileName)AND(OriginalSize * 8 /1024) NewSize BEGINOuter Counter = 0 WHILE((Counter OriginalSize / 16)AND(Counter 50000))BEGINupdate INSERT DummyTrans VALUES(39。)DELETE DummyTrans SELECT Counter = Counter + 1 END EXEC(TruncLog)END SELECT 39。 + db_name()+ 39。 + CONVERT(VARCHAR(30),size)+ 39。 + CONVERT(VARCHAR(30),(size*8/1024))+ 39。 FROM sysfiles WHERE name = LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF說明：更改某個表exec sp_changeobjectowner 39。,39。存儲更改全部表CREATE PROCEDURE OldOwner as NVARCHAR(128), NewOwner as NVARCHAR(128)AS DECLARE Name as NVARCHAR(128)DECLARE Owner as NVARCHAR(128)DECLARE OwnerName as NVARCHAR(128)DECLARE curObject CURSOR FOR select 39。 = name, 39。 = user_name(uid)from sysobjects where user_name(uid)=OldOwner order by name OPEN curObject FETCH NEXT FROM curObject INTO Name, Owner WHILE(FETCH_STATUS=0)BEGIN if Owner=OldOwner begin set OwnerName = OldOwner + 39。 + rtrim(Name)exec sp_changeobjectowner OwnerName, NewOwner endselect name,NewOwner,OldOwner FETCH NEXT FROM curObject INTO Name, Owner END close curObject deallocate curObject GOSQL SERVER中直接循環(huán)寫入數(shù)據(jù) declare i int set i=1 while i有如下表，要求就裱中所有沒有及格的成績，使他們剛好及格:Name score Zhangshan 80Lishi 59 Wangwu 50Songquan 69while((select min(score)from tb_table)if(select min(score)from tb_table)60 break else continue end數(shù)據(jù)開發(fā)經(jīng)典:Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //: select encrypt(39。)select pwdencrypt(39。)select pwdpare(39。,39。)= 1相同；否則不相同 encrypt(39。)select pwdencrypt(39。)select pwdpare(39。,39。)= 1相同；否則不相同 : declare list varchar(1000), sql nvarchar(1000)select list=list+39。+ from sysobjects a,syscolumns b where = and =39。 set sql=39。+right(list,len(list)1)+39。 exec(sql): EXEC master..xp_fixeddrives ,B表是否相等: if(select checksum_agg(binary_checksum(*))from A)=(select checksum_agg(binary_checksum(*))from B)print 39。 else print 39。 : DECLARE hcforeach CURSOR GLOBAL FOR SELECT 39。+RTRIM(spid)FROM WHERE program_name IN(39。,N39。)EXEC sp_msforeach_worker 39。 : 開頭到N條記錄Select Top N * From 表N到M條記錄(要有主索引ID)Select Top MN * From 表 Where ID in(Select Top M ID From 表)Order by ID DescN到結(jié)尾記錄Select Top N * From 表 Order by ID Desc 案例例如1：一張表有一萬多條記錄，表的第一個字段 RecID 是自增長字段，寫一個SQL語句，找出表的第31到第40個記錄。select top 10 recid from A where??是從索引中查找，而后面的select top 30 recid from A則在數(shù)據(jù)表中查找，這樣由于索引中的順序有可能和數(shù)據(jù)表中的不一致，這樣就導(dǎo)致查詢到的不是本來的欲得到的數(shù)據(jù)。set s = 39。 + str(count1)+ 39。 print sexec sp_executesqls 9：獲取當前數(shù)據(jù)庫中的所有用戶表select Name from sysobjects where xtype=39。 and status=0 10：獲取某一個表的所有字段select name from syscolumns where id=object_id(39。)select name from syscolumns where id in(select id from sysobjects where type = 39。 and name = 39。)兩種方式的效果相同11：查看與某一個表相關(guān)的視圖、存儲過程、函數(shù)select a.* from sysobjects a, sysments b where = and like 39。 12：查看當前數(shù)據(jù)庫中所有存儲過程select name as 存儲過程名稱 from sysobjects where xtype=39。 13：查詢用戶創(chuàng)建的所有數(shù)據(jù)庫select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name=39。)或者select dbid, name AS DB_NAME from master..sysdatabases where sid 0x01 14：查詢某一個表的字段和數(shù)據(jù)類型select column_name,data_type from where table_name = 39。 15：不同服務(wù)器數(shù)據(jù)庫之間的數(shù)據(jù)操作創(chuàng)建鏈接服務(wù)器exec sp_addlinkedserver 39。, 39。, 39。, 39。 exec sp_addlinkedsrvlogin 39。, 39。,null, 39。, 39。查詢示例select * from select * into 表 from exec sp_dropserver 39。, 39。連接遠程/局域網(wǎng)數(shù)據(jù)(openrowset/openquery/opendatasource)openrowset查詢示例select * from openrowset(39。, 39。39。39。,)生成本地表select * into 表 from openrowset(39。, 39。39。39。,)把本地表導(dǎo)入遠程表insert openrowset(39。, 39。39。39。,)select *from 本地表更新本地表 update b set = from openrowset(39。, 39。39。39。,)as a inner join 本地表 b on =exec sp_addlinkedserver 39。, 39。, 39。, 39。查詢select * FROM openquery(ITSV, 39。)把本地表導(dǎo)入遠程表insert openquery(ITSV, 39。)select * from 本地表更新本地表 update b set = FROM openquery(ITSV, 39。)as a inner join 本地表 b on =opendatasource/openrowset SELECT * FROM opendatasource(39。, 39。User ID=登陸名。).insert opendatasource(39。, 39。User ID=登陸名。). select * from 本地表SQL Server基本函數(shù)SQL Server基本函數(shù) 長度與分析用1,datalength(Char_expr)返回字符串包含字符數(shù),但不包含后面的空格2,substring(expression,start,length)取子串，字符串的下標是從“1”，start為起始位置，length為字符串長度，實際應(yīng)用中以len(expression)取得其長度3,right(char_expr,int_expr)返回字符串右邊第int_expr個字符，還用left于之相反4,isnull(check_expression , replacement_value)如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作類5,Sp_addtype 自定義數(shù)據(jù)類型例如：EXEC sp_addtype birthday, datetime, 39。 6,set nocount {on|off} 使返回的結(jié)果中不包含有關(guān)受 TransactSQL