在數(shù)據(jù)庫管理系統(tǒng)中，如何保證數(shù)據(jù)庫中的數(shù)據(jù)完整性是一項重要的課題。數(shù)據(jù)完整性是指存儲在數(shù)據(jù)庫的數(shù)據(jù)的一致性。主要體現(xiàn)在以下幾個方面：實體完整性（Entity Integrity）、域完整性（Domain Integrity）、參照完整性（Referential Integrity）和用戶的自定義完整性（User define Integrity）。目前，已有多種方法來解決這個問題。從最基本的數(shù)據(jù)類型，到多種形式的約束條件，雖然都提出了數(shù)據(jù)完整性的解決方案，但由于這些方法較為簡單，不能解決比較復雜的數(shù)據(jù)完整性問題。而觸發(fā)器（Trigger）作為一種高級的技術，可以輕松地解決任何有關保證數(shù)據(jù)完整性的問題。 
一、在SQL SERVER環(huán)境中使用觸發(fā)器 
　 　1觸發(fā)器的工作原理 
觸發(fā)器是一種特殊類型的存儲過程，它與表緊密聯(lián)系在一起，在對表進行插入、刪除和更新時，如該表（也稱觸發(fā)器表）有相應操作類型的觸發(fā)器，則觸發(fā)器便會自動觸發(fā)執(zhí)行。觸發(fā)器分為INSERT觸發(fā)器、DELETE觸發(fā)器和UPDATE觸發(fā)器3類。當向觸發(fā)器表中插入數(shù)據(jù)時，INSERT觸發(fā)器將觸發(fā)執(zhí)行，新的記錄會增加到觸發(fā)器表和inseted表中；當刪除觸發(fā)器表中的數(shù)據(jù)時，DELETE觸發(fā)器將觸發(fā)執(zhí)行，被刪除的記錄會存放到deleted表中；當更新觸發(fā)器表中的數(shù)據(jù)時，相當于插入一條新記錄和刪除一條舊記錄，此時UPDATE觸發(fā)器將觸發(fā)執(zhí)行，表中原有的記錄存放到deleted表中，修改后的記錄插入到inserted表中。其中inserted表和deleted表是兩個邏輯表，由系統(tǒng)來維護，不允許用戶直接對這兩個表進行修改。它們存放于內(nèi)存中，不存放在數(shù)據(jù)庫中。這兩個表的結構總是與被該觸發(fā)器作用的表的結構相同。觸發(fā)器工作完成后，與該觸發(fā)器相關的這兩個表也會被刪除。deleted表用于存儲SQL語言中DELETE和UPDATE語句所影響的行的復本。在執(zhí)行DELETE或 UPDATE語句時，行從觸發(fā)器表中刪除，并傳輸?shù)?nbsp;deleted 表中。deleted 表和觸發(fā)器表通常沒有相同的行；inserted 表用于存儲 SQL語言中INSERT 和 UPDATE 語句所影響的行的副本。在一個插入或更新事務處理中，新建行被同時添加到 inserted 表和觸發(fā)器表中。inserted 表中的行是觸發(fā)器表中新行的副本。 
　　2觸發(fā)器的實現(xiàn)步驟 
在筆者開發(fā)的教材管理系統(tǒng)中，建立了一個教材數(shù)據(jù)庫JCSJK，其中有教材表JCB和教材進出明細表MXB，需要在對MXB進行插入、刪除和修改時，動態(tài)地修改JCB中對應教材的庫存數(shù)量。下面舉例說明觸發(fā)器的實現(xiàn)步驟。為減少篇幅，對表結構作了簡化處理。以下操作在Microsoft SQL SERVER環(huán)境中完成。 
　　(1)建立教材表JCB，并定義主鍵 
CREATE TABLE ［dbo］.［JCB］ ( 
［教材代碼］ ［char］ (10) NOT NULL , 
［教材名］ ［char］ (30) NOT NULL , 
［價格］ ［decimal］(18, 2) NOT NULL , 
［庫存量］ ［int］ NULL , 
［出版社］ ［char］ (20) NULL  
) ON ［PRIMARY］ALTER TABLE ［dbo］.［JCB］ WITH NOCHECK ADD 
CONSTRAINT ［PK－JCB］ PRIMARY KEY CLUSTERED 
(［教材代碼］ 
) ON ［PRIMARY］ 
　　(2)建立教材進出明細表MXB，并定義主鍵和外?br> CREATE TABLE ［dbo］.［MXB］ ( 
［教材代碼］ ［char］ (10) NOT NULL , 
［日期］ ［datetime］ NOT NULL , 
［教材進］ ［int］ NULL , 
［教材出］ ［int］ NULL , 
［備注］ ［char］ (40) NULL  
) ON ［PRIMARY］ 
ALTER TABLE ［dbo］.［MXB］ WITH NOCHECK ADD 
CONSTRAINT ［PK－MXB］ PRIMARY KEY CLUSTERED  
(［教材代碼］, 
［日期］ 
) ON ［PRIMARY］ 
ALTER TABLE ［dbo］.［MXB］ ADD 
CONSTRAINT ［FK－MXB－JCB］ FOREIGN KEY 
(［教材代碼］) REFERENCES ［dbo］.［JCB］ ( 
［教材代碼］) ON DELETE CASCADE ON UPDATE CASCADE 
圖1顯示了JCB和MXB兩個表的關系。 
　(3)在MXB上建立INSERT觸發(fā)器 
CREATE TRIGGER MXB－INSE ON ［dbo］.［MXB］ 
FOR INSERT 
ASUPDATE JCB SET 庫存量=庫存量+ 
(SELECT 教材進-教材出 FROM INSERTED) 
FROM JCB,INSERTEDWHERE JCB.教材代碼=INSERTED.教材代碼 
(4)在MXB上建立DELETE觸發(fā)器 
CREATE TRIGGER MXB－DELE ON ［dbo］.［MXB］ 
FOR DELETE 
AS 
UPDATE JCB SET 庫存量=庫存量- 
(SELECT 教材進-教材出 FROM DELETED) 
FROM JCB,DELETEDWHERE JCB.教材代碼=DELETED.教材代碼〖HT〗 
　　(5)在MXB上建立UPDATE觸發(fā)器 
CREATE TRIGGER MXB－UPDA ON ［dbo］.［MXB］ 
FOR UPDATE 
AS 
BEGINUPDATE JCB SET 庫存量=庫存量- 
(SELECT 教材進-教材出 FROM DELETED) 
FROM JCB,DELETED 
WHERE JCB.教材代碼=DELETED.教材代碼 
UPDATE JCB SET 庫存量=庫存量+ 
(SELECT 教材進-教材出 FROM INSERTED) 
FROM JCB,INSERTEDWHERE JCB.教材代碼=INSERTED.教材代碼 
END 
　　通過以上步驟，設置了MXB表的3類觸發(fā)器，當用戶對MXB表進行插入、刪除和修改時，將根據(jù)MXB中教材進出的情況動態(tài)地修改JCB中對應教材的庫存量。由于在觸發(fā)器中，涉及到的inserted表和deleted表均存放在內(nèi)存中，因此，觸發(fā)器的執(zhí)行速度較快。 
　　3設計觸發(fā)器的考慮 
　　在寫觸發(fā)器代碼時需要考慮的一個重要問題就是，引發(fā)觸發(fā)器的語句可以是一個影響單行的語句，也可以是一個影響多行的語句。這在 UPDATE 和DELETE 觸發(fā)器中很常見，因為這些語句經(jīng)常作用于多行。而這在 INSERT 觸發(fā)器中就比較少見，因為基本的 INSERT 語句只添加一行。然而，由于 INSERT 觸發(fā)器可由 INSERT INTO (table_name) SELECT 語句激發(fā)，所以，插入許多行可能導致單個的觸發(fā)器調(diào)用。上面討論的涉及MXB的3類觸發(fā)器都是針對影響單行的語句。因此，有必要考慮影響多行的語句，這里對MXB的INSERT觸發(fā)器進行討論。 
　　(1)可處理多行的MXB上的INSERT觸發(fā)器 
　　如果要進行多行插入，上面示例中的觸發(fā)器可能就不能正確處理，因為 UPDATE 語句賦值表達式右邊的表達式只能是一個值，而不能是一個值列表。因此，該觸發(fā)器的作用就是獲取 inserted 表中任意一行的值，并將其添加到JCB表中特定教材代碼值的已有庫存量值上。如果某個教材代碼值在inserted 表中出現(xiàn)了多次，則可能無法得到預期的結果。為了正確地更新JCB表，觸發(fā)器就必須適應inserted表中出現(xiàn)多行的可能性。這可以通過 SUM 函數(shù)實現(xiàn)，它為 inserted 表中每個教材代碼計算教材進出的總計。SUM 函數(shù)存放于相關子查詢中（SELECT 語句在括號內(nèi)）。該子查詢?yōu)?nbsp;inserted 表中與JCB表的教材代碼匹配或相關的每個教材代碼返回一個單一值。 
CREATE TRIGGER MXB－INSE ON ［dbo］.［MXB］ 
FOR INSERT 
AS 
UPDATE JCB SET 庫存量=庫存量+ 
(SELECT SUM(教材進-教材出) FROM INSERTED 
WHERE JCB.教材代碼=INSERTED.教材代碼) 
WHERE JCB.教材代碼 IN 
(SELECT 教材代碼 FROM INSERTED) 
　　該觸發(fā)器對單行插入同樣適用，不過，使用該觸發(fā)器時，WHERE 子句中所使用的相關子查詢和 IN 運算符需要額外處理，而這對于單行插入來說是不必要的。 
　　(2)可區(qū)分單行和多行插入的MXB上的INSERT觸發(fā)器可以通過系統(tǒng)函數(shù)@@ROWCOUNT以區(qū)分單行插入和多行插入，以使觸發(fā)器針對不同行數(shù)使用最優(yōu)方法。 
CREATE TRIGGER MXB_INSE ON ［dbo］.［MXB］ 
FOR INSERT 
AS 
IF @@ROWCOUNT=1 
BEGIN 
UPDATE JCB SET 庫存量=庫存量+ 
(SELECT 教材進-教材出 FROM INSERTED) 
FROM JCB,INSERTED 
WHERE JCB.教材代碼=INSERTED.教材代碼 
END 
ELSE 
BEGIN 
UPDATE JCB SET 庫存量=庫存量+ 
(SELECT SUM(教材進-教材出) FROM INSERTED 
WHERE JCB.教材代碼=INSERTED.教材代碼) 
WHERE JCB.教材代碼 IN 
(SELECT 教材代碼 FROM INSERTED) 
END 
　　二、結論 
　　觸發(fā)器應用于支持企業(yè)級商業(yè)解決方案時，是一個功能十分強大的工具。它可以用于實現(xiàn)業(yè)務規(guī)則，可以檢查事務，可以在同一表上創(chuàng)建多個觸發(fā)器來分離代碼的功能。另外，通過使用觸發(fā)器收集的信息，可以提高數(shù)據(jù)庫的性能，可以用來維護那些使用外鍵所不能實現(xiàn)的復雜參數(shù)完整性。因此，對于維護數(shù)據(jù)表之間一致性，保持數(shù)據(jù)的相關完整性的情況，觸發(fā)器應作為首先考慮的技術。 
儲過程中返回結果 
從存儲過程中返回結果有三種方式： 
1、 返回結果集 
這是客戶端應用程序返回結果的最通用的方法。結果集是通過使用SELECT語句選擇數(shù)據(jù)產(chǎn)生的。結果集可以從永久表、臨時表或局部變量中產(chǎn)生。將結果返回到另一個存儲過程不是一種有效的方法。存儲過程不能訪問另一個存儲過程建立的結果集。 
例如從永久表中返回結果集： 
USE pubs 
GO 
CREATE PROCEDURE ap_CreateResultFromPermtable 
AS 
SELECT au_iname FROM authors 
GO 
例如從局部變量中創(chuàng)建結果集： 
USE pubs 
GO 
CREATE PROCEDURE ap_CreateResultFromVariable 
AS 
DECLARE @au_iname char(20) 
SELECT @au_iname = au_iname FROM authors 
WHERE au_id = ‘172-32-1176’ 
SELECT @au_id 
GO 
2、 設置OUTPUT參數(shù)的值 
輸出參數(shù)經(jīng)常用來從存儲過程中檢索出結果。如果某個參數(shù)在傳輸?shù)酱鎯^程中時被定義成OUTPUT，則對該參數(shù)的任何修改在退出存儲之后仍然有效。 
例如： 
USE pubs 
GO 
CREATE PROCEDURE ap_SetOutputVar @count integer OUTPUT 
AS 
SELECT @count = count(*) FROM authors 
GO 
從輸出參數(shù)中檢索出值： 
USE pubs 
GO 
CREATE PROCEDURE ap_GetOutputVar 
AS 
DECLARE @num integer 
EXECUTE ap_SetOutputVar @num OUTPUT 
PRINT “the count is”+convert(char,@num) 
GO 
? 將游標使用成OUTPUT參數(shù)。游標可以使用OUTPUT（輸出）參數(shù)，但不能使用成輸入?yún)?shù)。也就是說，游標可以作為結果返回，但卻不能傳輸?shù)竭^程中去。當游標被用作參數(shù)時，需要限定其為OUTPUT和VARYING。VARYING關鍵字指出該結果集要用來支持輸出參數(shù)。這樣就提供了將結果集返回到調(diào)用過程的能力。 
例如： 
USE pubs 
GO 
CREATE PROCEDURE GetTitleCount @count_cursor CURSOR VARYING OUTPUT 
AS 
SET @count_cursor = CURSOR 
FOR 
SELECT au_id,count(*) 
FROM titleauthors 
GROUP BY au_id 
OPEN @count_cursor 
GO 
3、 通過RETURN參數(shù)返回狀態(tài) 
這是一種從存儲過程返回錯誤碼的方法。存儲過程總是返回一個狀態(tài)值，用戶也可以使用RETURN語句返回自己的狀態(tài)。 
例如： 
USE pubs 
GO 
CREATE PROCEDURE ap_SetReturnStatus 
AS 
DECLARE @count integer 
SELECT @count = count(*) FROM authors 
IF @count = 0 
RETURN(1) 
ELSE 
RETURN (0) 
GO 
例如檢索出返回的狀態(tài)： 
USE pubs 
GO 
CREATE PROCEDURE ap_GetReturnStatus 
AS 
DECLARE @status integer 
EXECUTE @status = ap_SetReturnStatus 
IF @status = 1 
PRINT “No rows found” 
ELSE 
PRINT “successful” 
GO 
在存儲過程中進行錯誤處理 
如同其它程序一樣，在存儲過程中進行錯誤處理是非常重要的。系統(tǒng)變更@@error在執(zhí)行每一個Transact SQL語句之后都會得到一個值。對于成功的執(zhí)行，@@error的值為0，如果出現(xiàn)錯誤，則@@error中將包含錯誤信息。@@error系統(tǒng)變量對存儲過程的錯誤處理是非常重要的。 
注意：為了防止錯誤，@@error所能設置的值在sysmessages表的“error”中反映了出來。 
在存儲過程中的錯誤有兩種類型： 
1、 數(shù)據(jù)庫相關的錯誤 
這些錯誤是由數(shù)據(jù)庫的不一致性引起的，系統(tǒng)使用非0的@@error值表示特定的數(shù)據(jù)庫問題。在Transact SQL執(zhí)行之后，可以通過@@error獲得所出現(xiàn)的錯誤。如果發(fā)現(xiàn)@@error不為0，則必須采取必要的行動，大多數(shù)情況下，存儲將不再繼續(xù)進行處理而返回。下面的示例展示了典型的獲取數(shù)據(jù)庫錯誤的方法。該過程將錯誤代碼放置到輸出變量中，這樣，調(diào)用程序就能夠訪問到。 
USE pubs 
GO 
CREATE PROCEDURE ap_TrapDatabaseError @return_code integer OUTPUT 
AS 
UPDATE authors SET au_iname = “Jackson” 
WHERE au_iname = “Smith” 
IF @@error <> 0 
BEGIN 
SELECT @return_code = @@error 
RETURN 
END 
ELSE 
@return_code = 0 
GO 
2、 業(yè)務邏輯錯誤 
這些錯誤是由于違反了業(yè)務規(guī)則而引起的。要獲取這些錯誤，首先需要定義業(yè)務規(guī)則，基于這些規(guī)則，需要在存儲過程中增加必要的錯誤檢測代碼。人們經(jīng)常使用RAISERROR語句通報這些錯誤。RAISERROR提供了返回用戶定義錯誤及將@@error變量設置成用戶定義錯誤號的能力。錯誤消息可以被動態(tài)地建立，或者基于錯誤號從“sysmessages”表中檢索到。一旦出現(xiàn)了錯誤，錯誤就會以一種服務器錯誤消息的方式返回到客戶機。下面是RAISERROR命令的語法： 
RAISERROR (msg_id | msg_str, severity, state 
[, argument ][,…n])) 
[WITH options] 
Msg_id指明用戶定義消息的id，該消息存儲在“sysmessages”系統(tǒng)表中。 
Msg_str用于動態(tài)創(chuàng)建消息的消息字符串。這與C語言中的“printf”非常相似。 
Severity定義用戶賦值的錯誤消息嚴重程度。 
State是從1到127的任意整數(shù)值，它表示錯誤的調(diào)用狀態(tài)信息。負數(shù)的state值將缺省為1。 
OPTIONS指明錯誤的定制選項。OPTIONS的有效值如下： 
1） LOG。 
將錯誤記錄到服務器錯誤日志和NT事件日志中。該選項需要消息帶有從19到25的嚴重程度。而只有系統(tǒng)管理員才能發(fā)出這種消息。 
2） NOWAIT。 
將消息立即發(fā)送到客戶端服務器。 
3） SETERROR。 
不管其嚴重級別如何，將@@error的值設置為msg_id或5000。 
遠程過程調(diào)用 
SQL Server提供了調(diào)用駐留在不同服務器上的存儲過程的能力。調(diào)用這樣的存儲過程稱謂遠程存儲過程調(diào)用。為了使得調(diào)用能從一個SQL Server轉(zhuǎn)移到另一個服務器，兩個服務器應該相互定義成對方的有效遠程服務器。 
設置遠程服務器的配置： 
? 擴展某個服務器的組。 
? 右擊該服務器并點擊“Properties”。 
? 設置選項“Allow other SQL Servers to connect remotely to this SQL server via RPC”。 
? 設置“Query time out”選項的值，該值指定從一個查詢處理返回所能等待的秒數(shù)。缺省值為0，表示允許無限的等待時間。 
? 設置完成配置選項之后，點擊“OK”。 
? 重新啟動服務器之后，修改將會生效。 
? 在另一臺遠程服務器上重復相同的步驟。 
調(diào)用遠程存儲過程需要指明服務器的名稱，后帶數(shù)據(jù)庫的名稱和擁有者的名稱。下面是在不同的服務器（Server2）上調(diào)用一個存儲過程的示例。 
Exec server2.pubs.dbo.myproc 
豆豆的后話： 
這里只是粗淺的介紹了SQL Server常用的知識，對象也是基于SQL Server數(shù)據(jù)庫編寫應用程序的編程人員，而非數(shù)據(jù)庫管理者。但對于應用程序編程者，了解數(shù)據(jù)庫的管理也是非常有用的。建議在以后的時間自行去了解數(shù)據(jù)庫的管理，這對于優(yōu)化程序也是相當有用的。 
 
查看鎖信息 
create table #t(req_spid int,obj_name sysname) 
declare @s nvarchar(4000) 
,@rid int,@dbname sysname,@id int,@objname sysname 
declare tb cursor for 
select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid 
from master..syslockinfo where rsc_type in(4,5) 
open tb 
fetch next from tb into @rid,@dbname,@id 
while @@fetch_status=0 
begin 
set @objname=name from ['+@dbname+']..sysobjects where id=@id' 
exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id 
insert into #t values(@rid,@objname) 
fetch next from tb into @rid,@dbname,@id 
end 
close tb 
deallocate tb 
select 進程id=a.req_spid 
,數(shù)據(jù)庫=db_name(rsc_dbid) 
,類型=case rsc_type when 1 then 'NULL 資源（未使用）' when 2 then '數(shù)據(jù)庫' 
when 3 then '文件' 
when 4 then '索引' 
when 5 then '表' 
when 6 then '頁' 
when 7 then '鍵' 
when 8 then '擴展盤區(qū)' 
when 9 then 'RID（行 ID)' 
when 10 then '應用程序' 
end 
,對象id=rsc_objid 
,對象名=b.obj_name 
,rsc_indid 
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid 
go 
drop table #t 
 

Transact-SQL 參考 
sysobjects 
在數(shù)據(jù)庫內(nèi)創(chuàng)建的每個對象（約束、默認值、日志、規(guī)則、存儲過程等）在表中占一行。只有在 tempdb 內(nèi)，每個臨時對象才在該表中占一行。 
列名 數(shù)據(jù)類型 描述 
name sysname 對象名。 
Id int 對象標識號。 
xtype char(2) 對象類型?？梢允窍铝袑ο箢愋椭械囊环N： C = CHECK 約束 
D = 默認值或 DEFAULT 約束 F = FOREIGN KEY 約束 
L = 日志 
FN = 標量函數(shù) 
IF = 內(nèi)嵌表函數(shù) 
P = 存儲過程 
PK = PRIMARY KEY 約束（類型是 K） 
RF = 復制篩選存儲過程 
S = 系統(tǒng)表 
TF = 表函數(shù) 
TR = 觸發(fā)器 
U = 用戶表 
UQ = UNIQUE 約束（類型是 K） 
V = 視圖 
X = 擴展存儲過程 

uid smallint 所有者對象的用戶 ID。 
info smallint 保留。僅限內(nèi)部使用。 
status int 保留。僅限內(nèi)部使用。 
base_schema_ 
ver int 保留。僅限內(nèi)部使用。 
replinfo int 保留。供復制使用。 
parent_obj int 父對象的對象標識號（例如，對于觸發(fā)器或約束，該標識號為表 ID）。 
crdate datetime 對象的創(chuàng)建日期。 
ftcatid smallint 為全文索引注冊的所有用戶表的全文目錄標識符，對于沒有注冊的所有用戶表則為 0。 
schema_ver int 版本號，該版本號在每次表的架構更改時都增加。 
stats_schema_ 
ver int 保留。僅限內(nèi)部使用。 
type char(2) 對象類型?？梢允窍铝兄抵唬?nbsp;
C = CHECK 約束 
D = 默認值或 DEFAULT 約束 
F = FOREIGN KEY 約束 
FN = 標量函數(shù) 
IF = 內(nèi)嵌表函數(shù) 
K = PRIMARY KEY 或 UNIQUE 約束 
L = 日志 
P = 存儲過程 
R = 規(guī)則 
RF = 復制篩選存儲過程 
S = 系統(tǒng)表 
TF = 表函數(shù) 
TR = 觸發(fā)器 
U = 用戶表 
V = 視圖 
X = 擴展存儲過程 

userstat smallint 保留。 
sysstat smallint 內(nèi)部狀態(tài)信息。 
indexdel smallint 保留。 
refdate datetime 留作以后使用。 
version int 留作以后使用。 
deltrig int 保留。 
instrig int 保留。 
updtrig int 保留。 
seltrig int 保留。 
category int 用于發(fā)布、約束和標識。 
cache smallint 保留。 
查詢指定表在那些數(shù)據(jù)庫中存在 
declare @tbname sysname set 客戶資料' declare @dbname sysname,@sql nvarchar(4000),@re bit,@sql1 varchar(8000) set @sql1='' 
declare tb cursor for select name from master..sysdatabases 
open tb 
fetch next from tb into @dbname 
while @@fetch_status=0 
begin 
set @re=case when exists(select 1 from [' 
+@dbname+']..sysobjects where xtype=''U'' and name=''' 
+@tbname+''') then 1 else 0 end' 
exec sp_executesql @sql,N'@re bit out',@re out 
if @re=1 set @sql1=@sql1+' union all select '''+@dbname+'''' 
fetch next from tb into @dbname 
end 
close tb 
deallocate tb 
set @sql1=substring(@sql1,12,8000) 
exec(@sql1)   

比較兩個數(shù)據(jù)庫的表結構差異 
exec p_comparestructure 'xzkh_model','xzkh_new' 
--*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_comparestructure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
drop procedure [dbo].[p_comparestructure] 
GO 
create proc p_comparestructure 
@dbname1 varchar(250), --要比較的數(shù)據(jù)庫名1 
@dbname2 varchar(250) --要比較的數(shù)據(jù)庫名2 
as 
create table #tb1(表名1 varchar(250),字段名 varchar(250),序號 int,標識 bit,主鍵 bit,類型 varchar(250), 
占用字節(jié)數(shù) int,長度 int,小數(shù)位數(shù) int,允許空 bit,默認值 varchar(500),字段說明 varchar(500)) 
create table #tb2(表名2 varchar(250),字段名 varchar(250),序號 int,標識 bit,主鍵 bit,類型 varchar(250), 占用字節(jié)數(shù) int,長度 int,小數(shù)位數(shù) int,允許空 bit,默認值 varchar(500),字段說明 varchar(500)) --得到數(shù)據(jù)庫1的結構 
exec('insert into #tb1 SELECT 表名=d.name,字段名=a.name,序號=a.colid, 
標識=case when a.status=0x80 then 1 else 0 end, 主鍵=case when exists(SELECT 1 FROM '+@dbname1+'..sysobjects where xtype=''PK'' and name in (SELECT name FROM '+@dbname1+'..sysindexes WHERE indid in(SELECT indid FROM '+@dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid ))) then 1 else 0 end, 
類型=b.name, 占用字節(jié)數(shù)=a.length,長度=a.prec,小數(shù)位數(shù)=a.scale, 允許空=a.isnullable, 
默認值=isnull(e.text,''''''),字段說明=isnull(g.[value],'''''') FROM '+@dbname1+'..syscolumns a left join '+@dbname1+'..systypes b on a.xtype=b.xusertype inner join '+@dbname1+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties'' left join '+@dbname1+'..syscomments e on a.cdefault=e.id left join '+@dbname1+'..sysproperties g on a.id=g.id and a.colid=g.smallid 
order by a.id,a.colorder') --得到數(shù)據(jù)庫2的結構 
exec('insert into #tb2 SELECT表名=d.name,字段名=a.name,序號=a.colid, 
標識=case when a.status=0x80 then 1 else 0 end, 主鍵=case when exists(SELECT 1 FROM '+@dbname2+'..sysobjects where xtype=''PK'' and name in (SELECT name FROM '+@dbname2+'..sysindexes WHERE indid in(SELECT indid FROM '+@dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid ))) then 1 else 0 end, 
類型=b.name, 占用字節(jié)數(shù)=a.length,長度=a.prec,小數(shù)位數(shù)=a.scale, 允許空=a.isnullable, 
默認值=isnull(e.text,''''''),字段說明=isnull(g.[value],'''''') 
FROM '+@dbname2+'..syscolumns a left join '+@dbname2+'..systypes b on a.xtype=b.xusertype 
inner join '+@dbname2+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties'' 
left join '+@dbname2+'..syscomments e on a.cdefault=e.id left join '+@dbname2+'..sysproperties g on a.id=g.id and a.colid=g.smallid order by a.id,a.colorder') 
--and not exists(select 1 from #tb2 where 表名2=a.表名1) 
select 比較結果=case when a.表名1 is null and b.序號=1 then '庫1缺少表：'+b.表名2 
when b.表名2 is null and a.序號=1 then '庫2缺少表:'+a.表名1 when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then '庫1 ['+b.表名2+'] 缺少字 段：'+b.字段名when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then '庫2 ['+a.表名1+'] 缺少字段：'+a.字段名 when a.標識<>b.標識 then '標識不同' when a.主鍵<>b.主鍵 then '主鍵設置不同' when a.類型<>b.類型 then '字段類型不同' when a.占用字節(jié)數(shù)<>b.占用字節(jié)數(shù) then '占用字節(jié)數(shù)' when a.長度<>b.長度 then '長度不同' when a.小數(shù)位數(shù)<>b.小數(shù)位數(shù) then '小數(shù)位數(shù)不同' when a.允許空<>b.允許空 then '是否允許空不同' 
when a.默認值<>b.默認值 then '默認值不同' when a.字段說明<>b.字段說明 then '字段說明不同' else '' end, * from #tb1 a full join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名 
where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null or a.標識<>b.標識 or a.主鍵<>b.主鍵 or a.類型<>b.類型 or a.占用字節(jié)數(shù)<>b.占用字節(jié)數(shù) or a.長度<>b.長度 or a.小數(shù)位數(shù)<>b.小數(shù)位數(shù) or a.允許空<>b.允許空 or a.默認值<>b.默認值 or a.字段說明<>b.字段說明 order by isnull(a.表名1,b.表名2),isnull(a.序號,b.序號)--isnull(a.字段名,b.字段名) go 
sql 系統(tǒng)存儲過程xp_cmdshell使用 
xp_cmdshell 
以操作系統(tǒng)命令行解釋器的方式執(zhí)行給定的命令字符串，并以文本行方式返回任何輸出。授予非管理用戶執(zhí)行 xp_cmdshell 的權限。 
說明 在 Microsoft? Windows? 95 或 Microsoft Windows 98 操作系統(tǒng)中執(zhí)行 xp_cmdshell 時，將不把 xp_cmdshell 的返回代碼設置為喚醒調(diào)用的可執(zhí)行文件的進程退出代碼。返回代碼始終為 0。 
語法 
xp_cmdshell {'command_string'} [, no_output] 
參數(shù) 
'command_string' 
是在操作系統(tǒng)命令行解釋器上執(zhí)行的命令字符串。command_string 的數(shù)據(jù)類型為 varchar(255) 或 nvarchar(4000)，沒有默認值。command_string 不能包含一對以上的雙引號。如果由 command_string 引用的文件路徑或程序名稱中有空格，則需要使用一對引號。如果使用嵌入空格不方便，可考慮使用 FAT 8.3 文件名作為解決辦法。 
no_output 
是可選參數(shù)，表示執(zhí)行給定的 command_string，但不向客戶端返回任何輸出。 
返回代碼值 
0（成功）或 1（失?。?nbsp;
結果集 
執(zhí)行下列 xp_cmdshell 語句將返回當前目錄的目錄列表。 
xp_cmdshell 'dir *.exe' 
行以 nvarchar(255) 列的形式返回。 
執(zhí)行下列 xp_cmdshell 語句將返回隨后的結果集： 
xp_cmdshell 'dir *.exe', NO_OUTPUT 
下面是結果： 
The command(s) completed successfully. 
注釋 
xp_cmdshell 以同步方式操作。在命令行解釋器命令執(zhí)行完畢之前，不會返回控制。 
當授予用戶執(zhí)行權限時，用戶能在 Microsoft Windows NT? 命令行解釋器上執(zhí)行運行 Microsoft SQL Server? 的帳戶有權執(zhí)行的任何操作系統(tǒng)命令。 
默認情況下，只有 sysadmin 固定服務器角色的成員才能執(zhí)行此擴展存儲過程。但是，也可以授予其他用戶執(zhí)行此存儲過程的權限。 
當作為 sysadmin 固定服務器角色成員的用戶喚醒調(diào)用 xp_cmdshell 時，將在運行 SQL Server 服務的安全上下文中執(zhí)行 xp_cmdshell。當用戶不是 sysadmin 組的成員時，xp_cmdshell 將模擬使用 xp_sqlagent_proxy_account 指定的 SQL Server 代理程序的代理帳戶。如果代理帳戶不能用，則 xp_cmdshell 將失敗。這只是針對于 Microsoft? Windows NT? 4.0 和 Windows 2000。在 Windows 9.x 上，沒有模擬，且 xp_cmdshell 始終在啟動 SQL Server 的 Windows 9.x 用戶的安全上下文下執(zhí)行。 
說明 在早期版本中，獲得 xp_cmdshell 執(zhí)行權限的用戶在 MSSQLServer 服務的用戶帳戶上下文中運行命令?？梢酝ㄟ^配置選項配置 SQL Server，以便對 SQL Server 無 sa 訪問權限的用戶能夠在 SQLExecutiveCmdExec Windows NT 帳戶的上下文中運行 xp_cmdshell。在 SQL Server 7.0 中，該帳戶稱為 SQLAgentCmdExec。現(xiàn)在，不是 sysadmin 固定服務器角色成員的用戶將在該帳戶上下文中運行命令，而無需再進行配置更改。 
權限 
xp_deletemail 的執(zhí)行權限默認授予 sysadmin 固定服務器角色的成員，但可以授予其他用戶。 
重要 如果為 MSSQLServer 服務選用的 Windows NT 帳戶不是本地管理員組的成員，則非 sysadmin 固定服務器角色成員的用戶將無法執(zhí)行 xp_cmdshell。 
示例 
A. 返回可執(zhí)行文件列表 
下例顯示執(zhí)行目錄命令的 xp_cmdshell 擴展存儲過程。 
EXEC master..xp_cmdshell 'dir *.exe' 
B. 使用 Windows NT net 命令 
下例顯示 xp_cmdshell 在存儲過程中的使用。下例先用 net send 通知用戶 SQL Server 即將關閉，然后用 net pause 暫停服務器，最后用 net stop 關閉服務器。 
CREATE PROC shutdown10 
AS 
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down 
in 10 minutes. No more connections allowed.', no_output 
EXEC xp_cmdshell 'net pause sqlserver' 
WAITFOR DELAY '00:05:00' 
EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server shutting down 
in 5 minutes.', no_output 
WAITFOR DELAY '00:04:00' 
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down 
in 1 minute. Log off now.', no_output 
WAITFOR DELAY '00:01:00' 
EXEC xp_cmdshell 'net stop sqlserver', no_output 
C. 不返回輸出 
下例使用 xp_cmdshell 執(zhí)行命令字符串，且不向客戶端返回輸出。 
USE master 
EXEC xp_cmdshell 'copy c:\sqldumps\pubs.dmp \\server2\backups\sqldumps', 
NO_OUTPUT 
D. 使用返回狀態(tài) 
在下例中，xp_cmdshell 擴展存儲過程也給出了返回狀態(tài)。返回代碼值存儲在變量 @result 中。 
DECLARE @result int 
EXEC @result = xp_cmdshell 'dir *.exe' 
IF (@result = 0) 
PRINT 'Success' 
ELSE 
PRINT 'Failure' 
E. 將變量內(nèi)容寫入文件 
下例將當前目錄內(nèi)容寫入當前服務器目錄下名為 dir_out.txt 的文件中。 
DECLARE @cmd sysname, @var sysname 
SET @var = 'dir /p' 
SET @cmd = 'echo ' + @var + ' > dir_out.txt' 
EXEC master..xp_cmdshell @cmd 
SQL 2K 安全指南 
數(shù)據(jù)庫是電子商務、金融以及ERP系統(tǒng)的基礎，通常都保存著重要的商業(yè)伙伴和客戶信息。大多數(shù)企業(yè)、組織以及政府部門的電子數(shù)據(jù)都保存在各種數(shù)據(jù)庫中，他們用這些數(shù)據(jù)庫保存一些個人資料，比如員工薪水、個人資料等等。數(shù)據(jù)庫服務器還掌握著敏感的金融數(shù)據(jù)。包括交易記錄、商業(yè)事務和賬號數(shù)據(jù)，戰(zhàn)略上的或者專業(yè)的信息，比如專利和工程數(shù)據(jù)，甚至市場計劃等等應該保護起來防止競爭者和其他非法者獲取的資料。數(shù)據(jù)完整性和合法存取會受到很多方面的安全威脅，包括密碼策略、系統(tǒng)后門、數(shù)據(jù)庫操作以及本身的安全方案。但是數(shù)據(jù)庫通常沒有象操作系統(tǒng)和網(wǎng)絡這樣在安全性上受到重視。 
微軟的SQL Server是一種廣泛使用的數(shù)據(jù)庫，很多電子商務網(wǎng)站、企業(yè)內(nèi)部信息化平臺等都是基于SQL Server上的，但是數(shù)據(jù)庫的安全性還沒有被人們更系統(tǒng)的安全性等同起來，多數(shù)管理員認為只要把網(wǎng)絡和 
操作系統(tǒng)的安全搞好了，那么所有的應用程序也就安全了。大多數(shù)系統(tǒng)管理員對數(shù)據(jù)庫不熟悉而數(shù)據(jù)庫管理員有對安全問題關心太少，而且一些安全公司也忽略數(shù)據(jù)庫安全，這就使數(shù)據(jù)庫的安全問題更加嚴峻了。數(shù)據(jù)庫系統(tǒng)中存在的安全漏洞和不當?shù)呐渲猛ǔ斐蓢乐氐暮蠊?，而且都難以發(fā)現(xiàn)。數(shù)據(jù)庫應用程序通常同操作系統(tǒng)的最高管理員密切相關。廣泛SQL Server數(shù)據(jù)庫又是屬于“端口”型的數(shù)據(jù)庫，這就表示任何人都能夠用分析工具試圖連接到數(shù)據(jù)庫上，從而繞過操作系統(tǒng)的安全機制，進而闖入系統(tǒng)、破壞和竊取數(shù)據(jù)資料，甚至破壞整個系統(tǒng)。 
這里，我們主要談論有關SQL Server2000數(shù)據(jù)庫的安全配置以及一些相關的安全和使用上的問題。 
在進行SQL Server 2000數(shù)據(jù)庫的安全配置之前，首先你必須對操作系統(tǒng)進行安全配置，保證你的操作系統(tǒng)處于安全狀態(tài)。然后對你要使用的操作數(shù)據(jù)庫軟件（程序）進行必要的安全審核，比如對ASP、PHP等腳本，這是很多基于數(shù)據(jù)庫的WEB應用常出現(xiàn)的安全隱患，對于腳本主要是一個過濾問題，需要過濾一些類似 , ‘ ; @ / 等字符，防止破壞者構造惡意的SQL語句。接著，安裝SQL Server2000后請打上補丁sp1以及最新的sp2。 
下載地址是：http://www.microsoft.com/sql/downlo...ds/2000/sp2.asp 
在做完上面三步基礎之后，我們再來討論SQL Server的安全配置。
1、使用安全的密碼策略 
我們把密碼策略擺在所有安全配置的第一步，請注意，很多數(shù)據(jù)庫賬號的密碼過于簡單，這跟系統(tǒng)密碼過于簡單是一個道理。對于sa更應該注意，同時不要讓sa賬號的密碼寫于應用程序或者腳本中。健壯的密碼是安全的第一步！ 
SQL Server2000安裝的時候，如果是使用混合模式，那么就需要輸入sa的密碼，除非你確認必須使用空密碼。這比以前的版本有所改進。 
同時養(yǎng)成定期修改密碼的好習慣。數(shù)據(jù)庫管理員應該定期查看是否有不符合密碼要求的賬號。比如使用下面的SQL語句： 
Use master 
Select name,Password from syslogins where password is null 
2、使用安全的賬號策略。 
由于SQL Server不能更改sa用戶名稱，也不能刪除這個超級用戶，所以，我們必須對這個賬號進行最強的保護，當然，包括使用一個非常強壯的密碼，最好不要在數(shù)據(jù)庫應用中使用sa賬號，只有當沒有其它方法登錄到 SQL Server 實例（例如，當其它系統(tǒng)管理員不可用或忘記了密碼）時才使用 sa。建議數(shù)據(jù)庫管理員新建立一個擁有與sa一樣權限的超級用戶來管理數(shù)據(jù)庫。安全的賬號策略還包括不要讓管理員權限的賬號泛濫。 
SQL Server的認證模式有Windows身份認證和混合身份認證兩種。如果數(shù)據(jù)庫管理員不希望操作系統(tǒng)管理員來通過操作系統(tǒng)登陸來接觸數(shù)據(jù)庫的話，可以在賬號管理中把系統(tǒng)賬號“BUILTIN\Administrators”刪除。不過這樣做的結果是一旦sa賬號忘記密碼的話，就沒有辦法來恢復了。 
很多主機使用數(shù)據(jù)庫應用只是用來做查詢、修改等簡單功能的，請根據(jù)實際需要分配賬號，并賦予僅僅能夠滿足應用要求和需要的權限。比如，只要查詢功能的，那么就使用一個簡單的public賬號能夠select就可以了。 
3、加強數(shù)據(jù)庫日志的記錄。 
審核數(shù)據(jù)庫登錄事件的“失敗和成功”，在實例屬性中選擇“安全性”，將其中的審核級別選定為全部，這樣在數(shù)據(jù)庫系統(tǒng)和操作系統(tǒng)日志里面，就詳細記錄了所有賬號的登錄事件。如圖： 
請定期查看SQL Server日志檢查是否有可疑的登錄事件發(fā)生，或者使用DOS命令。 
findstr /C:"登錄" d:\Microsoft SQL Server\MSSQL\LOG\*.* 
4、管理擴展存儲過程 
對存儲過程進行大手術，并且對賬號調(diào)用擴展存儲過程的權限要慎重。其實在多數(shù)應用中根本用不到多少系統(tǒng)的存儲過程，而SQL Server的這么多系統(tǒng)存儲過程只是用來適應廣大用戶需求的，所以請刪除不必要的存儲過程，因為有些系統(tǒng)的存儲過程能很容易地被人利用起來提升權限或進行破壞。 
如果你不需要擴展存儲過程xp_cmdshell請把它去掉。使用這個SQL語句： 
use master 
sp_dropextendedproc 'xp_cmdshell' 
xp_cmdshell是進入操作系統(tǒng)的最佳捷徑，是數(shù)據(jù)庫留給操作系統(tǒng)的一個大后門。如果你需要這個存儲過程，請用這個語句也可以恢復過來。 sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll' 
如果你不需要請丟棄OLE自動存儲過程（會造成管理器中的某些特征不能使用），這些過程包括如下： 
Sp_OACreate Sp_OADestroy Sp_OAGetErrorInfo Sp_OAGetProperty 
Sp_OAMethod Sp_OASetProperty Sp_OAStop 
去掉不需要的注冊表訪問的存儲過程，注冊表存儲過程甚至能夠讀出操作系統(tǒng)管理員的密碼來，如下： 
Xp_regaddmultistring Xp_regdeletekey Xp_regdeletevalue Xp_regenumvalues 
Xp_regread Xp_regremovemultistring Xp_regwrite 
還有一些其他的擴展存儲過程，你也最好檢查檢查。 
在處理存儲過程的時候，請確認一下，避免造成對數(shù)據(jù)庫或應用程序的傷害。 
5、使用協(xié)議加密 
SQL Server 2000使用的Tabular Data Stream協(xié)議來進行網(wǎng)絡數(shù)據(jù)交換，如果不加密的話，所有的網(wǎng)絡傳輸都是明文的，包括密碼、數(shù)據(jù)庫內(nèi)容等等，這是一個很大的安全威脅。能被人在網(wǎng)絡中截獲到他們需要的東西，包括數(shù)據(jù)庫賬號和密碼。所以，在條件容許情況下，最好使用SSL來加密協(xié)議，當然，你需要一個證書來支持。 
6、不要讓人隨便探測到你的TCP/IP端口 
默認情況下，SQL Server使用1433端口監(jiān)聽，很多人都說SQL Server配置的時候要把這個端口改變，這樣別人就不能很容易地知道使用的什么端口了。可惜，通過微軟未公開的1434端口的UDP探測可以很容易知道SQL Server使用的什么TCP/IP端口了（請參考《深入探索SQL Server網(wǎng)絡連接的安全問題》）。 
不過微軟還是考慮到了這個問題，畢竟公開而且開放的端口會引起不必要的麻煩。在實例屬性中選擇TCP/IP協(xié)議的屬性。選擇隱藏 SQL Server 實例。如果隱藏了 SQL Server 實例，則將禁止對試圖枚舉網(wǎng)絡上現(xiàn)有的 SQL Server 實例的客戶端所發(fā)出的廣播作出響應。這樣，別人就不能用1434來探測你的TCP/IP端口了（除非用Port Scan）。 
7、修改TCP/IP使用的端口 
請在上一步配置的基礎上，更改原默認的1433端口。在實例屬性中選擇網(wǎng)絡配置中的TCP/IP協(xié)議的屬性，將TCP/IP使用的默認端口變?yōu)槠渌丝凇Ｈ鐖D： 
9、拒絕來自1434端口的探測 
由于1434端口探測沒有限制，能夠被別人探測到一些數(shù)據(jù)庫信息，而且還可能遭到DOS攻擊讓數(shù)據(jù)庫服務器的CPU負荷增大，所以對Windows 2000操作系統(tǒng)來說，在IPSec過濾拒絕掉1434端口的UDP通訊，可以盡可能地隱藏你的SQL Server。 
10、對網(wǎng)絡連接進行IP限制 
SQL Server 2000數(shù)據(jù)庫系統(tǒng)本身沒有提供網(wǎng)絡連接的安全解決辦法，但是Windows 2000提供了這樣的安全機制。使用操作系統(tǒng)自己的IPSec可以實現(xiàn)IP數(shù)據(jù)包的安全性。請對IP連接進行限制，只保證自己的IP能夠訪問，也拒絕其他IP進行的端口連接，把來自網(wǎng)絡上的安全威脅進行有效的控制。 
關于IPSec的使用請參看：http://www.microsoft.com/china/tech...ty/ipsecloc.asp 
上面主要介紹的一些SQL Server的安全配置，經(jīng)過以上的配置，可以讓SQL Server本身具備足夠的安全防范能力。當然，更主要的還是要加強內(nèi)部的安全控制和管理員的安全培訓，而且安全性問題是一個長期的解決過程，還需要以后進行更多的安全維護。