Recycle手札

Recycle

SELECT T.NAME AS TABLE_NAME, C.NAME AS COLUMN_NAME, K.COLID, I.NAME AS INDEX_NAME, I.INDID, K.KEYNO
FROM SYSINDEXES AS I LEFT JOIN SYSOBJECTS AS T ON I.ID = T.ID LEFT JOIN SYSINDEXKEYS AS K ON I.ID = K.ID AND I.INDID = K.INDID LEFT JOIN SYSCOLUMNS AS C ON I.ID = C.ID AND K.COLID = C.COLID
WHERE T.XTYPE = 'U' AND (I.NAME LIKE 'PK_%' OR I.NAME LIKE 'IX_%')
ORDER BY T.NAME, I.INDID, K.KEYNO, C.NAME

Recycle

SELECT NAME, DBID, SID, MODE, STATUS, STATUS2, CRDATE, RESERVED, CATEGORY, CMPTLEVEL, FILENAME, VERSION, ' ' AS YN
FROM master.dbo.sysdatabases
ORDER BY NAME

Recycle

以下做法可操作Sql Server遠端主機之資料庫
Step1.必須先將連線的 ANSI_NULLS 與 ANSI_WARNINGS 選項開啟，它才能執行分散式查詢
資料程式庫應用程式連線會將 SET ANSI_WARNINGS 設定為 OFF，所以須手動開啟


SET ANSI_NULLS ON
SET ANSI_WARNINGS ON 
或
SET ANSI_DEFAULTS ON

Step2.連接遠端SQL資料庫


'WINSERVER':Sql Server之執行個體，亦可輸入ip，當輸入ip時會自動抓取該台主機之預設值行個體
指定執行個體之方式：'WINSERVER\SQL2005'或'192.168.0.1'或'192.168.0.1\SQL2005'
EXEC sp_addlinkedserver     'WINSERVER',    N'SQL Server'
exec   sp_addlinkedsrvlogin   'WINSERVER', false, null,'sa','pw'

Step3.操作遠端資料庫之語法


select   *   from   [WINSERVER].STDB_MCN.DBO.FDRCUSMI

Step4.登出遠端資料庫


EXEC  sp_droplinkedsrvlogin 'WINSERVER', NULL
EXEC  sp_droplinkedsrvlogin 'WINSERVER', 'sa'
exec sp_dropserver 'WINSERVER'

Step5.最後需將開啟之選項關閉


SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF 
或
SET ANSI_DEFAULTS OFF

PS：登入與登出動作可將之寫成STORE PROCDURE


CREATE PROCEDURE [dbo].[PROC_LoginSQLServer]
AS 
 --Link不存在才建立連結 
 if not exists( select * from master.dbo.sysservers where srvname= '10.24.13.26')
 begin
   EXEC sp_addlinkedserver     '10.24.13.26',    N'SQL Server'
   exec   sp_addlinkedsrvlogin   '10.24.13.26', false, null,'sa','admin' 
 end


CREATE PROCEDURE [dbo].[PROC_LogOutSQLServer]
AS 
 --Link存在才刪除連結
 if exists( select * from master.dbo.sysservers where srvname= '10.24.13.26')
 begin 
   EXEC  sp_droplinkedsrvlogin '10.24.13.26', NULL
   EXEC  sp_droplinkedsrvlogin '10.24.13.26', 'sa'
  exec sp_dropserver '10.24.13.26'  
 end

NOTE:
//判斷是否有註冊過(登陸遠端機器_sp_addlinkedserver)
select * from master.dbo.sysservers where srvname= '127.0.0.1'

Recycle

用SQL2000所提供的北風資料庫的訂單資料表(Orders)一範例。
如果要尋找每位客戶最後一次下訂單的那筆資料，試以下語法(應該會有90筆資料)：

SELECT o1.*
FROM Orders o1 INNER JOIN  
(SELECT CustomerID, MAX(OrderDate) AS OrderDate  
 FROM Orders  
 GROUP BY CustomerID) o2 
 ON o1.CustomerID = o2.CustomerID AND o1.OrderDate = o2.OrderDate