非常用SQL語句技巧總結

1.為數據表添加一個新欄位

Alter TABLE [dbo].[CustomerBackupConfig] Add [Stamp] [timestamp] NULL
GO

2.為數據表添加兩個新欄位

Alter Table tblStates Add [TaxRate] money not null default 0,
[Transit] int not null default 0
GO

3.為數據表刪除一個欄位

Alter Table [dbo].[tblOrder] Drop Column [CookieID]
GO

4.修改數據表一個欄位的定義

Alter Table [dbo].[tblOrder] Alter Column [CookieID] int not null
GO

5.刪除數據表

Drop Table tblTaxRate
GO

6.為數據表去掉一個外鍵關聯，然後再添加一個外鍵關聯

Alter TABLE [dbo].[OrderPackageOption] Drop CONSTRAINT [FK_OrderPackageOption_OrderPackage]


ALTER TABLE [dbo].[OrderPackageOption] ADD
CONSTRAINT [FK_OrderPackageOption_OrderPackage] FOREIGN KEY
(
[OrderPackageID]
) REFERENCES [dbo].[OrderPackage] (
[OrderPackageID]
)
GO

7. 為數據表添加兩個外鍵關聯，並且設置其中之一不檢查現有數據是否符合關聯要求。

ALTER TABLE [dbo].[Account] ADD
CONSTRAINT [FK_Account_State] FOREIGN KEY
(
[StateID]
) REFERENCES [dbo].[State] (
[StateID]
),
CONSTRAINT [FK_Account_TimeZones] FOREIGN KEY
(
[TimeZoneID]
) REFERENCES [dbo].[TimeZones] (
[timeid]
)
GO

Alter table [dbo].[Account] NoCheck Constraint [FK_Account_TimeZones]
GO

8. 增加外鍵時不檢查當前數據

ALTER TABLE [dbo].[Account] With NoCheck
ADD CONSTRAINT [FK_Account_AccountType] 
FOREIGN KEY
(
[TypeID]
) REFERENCES [dbo].[AccountType] (
[TypeID]
)
GO

9．為了對數據表進行批量的數據導入，需要將identity_insert先打開，執行完後在關閉。其目的是避開自增列的檢查，順利執行插入操作。

set identity_insert [dbo].[PSTNUsageNotes] on
--在這裡執行對PSTNUsageNotes表的批量插入操作
set identity_insert [dbo].[PSTNUsageNotes] off

10.暫時關閉約束檢查，等數據操作完成後再打開。

ALTER TABLE [Usage] NOCHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN1]
ALTER TABLE [Usage] NOCHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN2]
-- 在這裡執行有可能破壞外鍵約束的操作，如強行刪除數據等
ALTER TABLE [Usage] CHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN1]
ALTER TABLE [Usage] CHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN2]

11. 數據表的列重命名

exec sp_rename 'dbo.LocationIncidentalServiceRelationship.UsageAllowedWithouReservation','UsageAllowedWithoutReservation','column'

12. 檢查A資料庫中有哪些存儲過程在B資料庫中不存在

select name from Jaguar.dbo.sysobjects where xtype='P' and name not in
(select name from JaguarTest.dbo.sysobjects where xtype='P' )

13. 在全部用戶表和存儲過程中尋找包含某段文字的用戶表和存儲過程

select OBJECT_NAME(id) AS name,[name] as content,xtype from sysobjects
where [name] like '%IncidentalService%' And (xtype='U' or xtype='P')

類型說明：