摘要：
最近，我编写了SQL脚本，以便在执行安装包时自动生成库。我发现没有正在编写的SQL脚本，也没有特定的规范。剧本可能会伤害同事和我自己。所以我总结了以下内容以供参考。
最近在做安装包的时候编写sql脚本自动生成库，发现没有在写sql脚本没有一定的规范，写出来的脚本可能会害了同事和自己，所以总结了以下内容，以供参考。
1、创建表的脚本范例
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DIMRoles]') AND xtype = 'U')
BEGIN
CREATE TABLE [DIMRoles](
[Key] [int] NOT NULL,
[Name] [nvarchar](256) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Key] ASC
) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[Name] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
注意事项：
A、一定要先判断表是否存在，不存在才创建，而不能存在删除原表进行新表创建，重复执行会导致客户数据丢失！
2、修改表范例
2.1增加字段，增加控制（索引，不能为空等等）
IF NOT exists(select * from syscolumns where id=object_id('DIMUsers') and name='Phone')
BEGIN
ALTER TABLE dbo.DIMUsers ADD Phone NVARCHAR(20) NOT NULL DEFAULT('')
END
注意事项：
A、一定要判断字段是否存在表，不存在才创建。
B、一定看清楚判断的字段和表是否一样，不要上面判断A表，下面修改B表。
2.2修改删除字段
修改：
IF exists(select * from syscolumns where id=object_id('DIMUsers') and name='Phone')
BEGIN
ALTER TABLE dbo.DIMUsers ALTER COLUMN Phone NVARCHAR(50)
END
删除：
IF exists(select * from syscolumns where id=object_id('DIMUsers') and name='Phone')
BEGIN
ALTER TABLE dbo.DIMUsers DROP COLUMN Phone
END
注意事项：
A、一定要判断表的字段存在，不存在的话不存在修改和删除的意义。
3、初始化表数据范例
注意事项：
1、切勿使用delete 你要插入的表没有加上条件。
2、如果自增长字段最好关闭自增长，然后删除先插入你要加入到这张表的数据，因为这个ID是固定的。
SET IDENTITY_INSERT DIMUsers ON
DELETE FROM DIMUsers WHERE [Key] IN(1,2,3,4);
INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)
values (1, N'sa', N'123', 0, N'系统管理员', N'SA', getdate());
INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)
values (2, N'administrator', N'', 0, N'系统管理员', N'ADMINISTRATOR', getdate());
INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)
values (3, N'admin', N'123', 0, N'系统管理员', N'ADMIN', getdate());
INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)
values (4, N'public', N'', 1, N'公共聊天室', N'PUBLIC', getdate());
SET IDENTITY_INSERT DIMUsers OFF
如果系统的主键不是自增长的最好办了，上面的例子是最难办的自增长例子。
4、修改函数、存储过程、视图、索引
注意事项：
A、因为这些东西重复更新的话可以删除原来的保证最新的就可以，所以只需要判断是否存在即可。
函数示例：
IF OBJECT_ID('FUN_GetChildList') IS NOT NULL
DROP FUNCTION FUN_GetChildList
GO
/****** 对象: UserDefinedFunction [dbo].[FUN_GetChildList] 脚本日期: 07/05/2011 09:40:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
author:wenjl
createtime:2011-4-29
desc:--用途 根据传递的参数获取所有子节点
--取法举例SELECT * FROM FUN_GetChildList('COMPANY','TQ00000100')
*/
CREATE FUNCTION [dbo].[FUN_GetChildList] (
@Table Nvarchar(100),
@Value Nvarchar(100)
)
RETURNS @tResult TABLE (
--定义结果表
RESULT Nvarchar(100)
)
AS
BEGIN
--子类型表、父类型表、子类型数
DECLARE @tChild TABLE(RESULT Nvarchar(100))
DECLARE @tFather TABLE(RESULT Nvarchar(100))
DECLARE @Count SmallInt
--预先置传入的值到@tFather中
INSERT INTO @tFather
VALUES (@Value)
WHILE 1 = 1
BEGIN
--循环中，先将@tFather中内容放到@tResult中；
INSERT INTO @tResult SELECT RESULT FROM @tFather
--将@tChild清空，再取@tFather的所有子类型到@tChild中；
DELETE FROM @tChild
--根据不同的表追加写到IF内部的语句
---------------------------------------------------------------------------------------
IF @Table ='DIMDEPT'
BEGIN
INSERT INTO @tChild
SELECT RESULT = did
FROM DIMDEPT
WHERE pdid IN (SELECT RESULT FROM @tFather)
END
---------------------------------------------------------------------------------------
--当@tChild空时，退出；
SELECT @Count = COUNT(*) FROM @tChild
IF @Count = 0
BREAK
--将@tFather清空，并将@tChild中内容给@tFather。
DELETE FROM @tFather
INSERT INTO @tFather SELECT RESULT FROM @tChild
END
RETURN
END
存储过程示例：
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DIMAddFriend]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROC DIMAddFriend
GO
create proc [dbo].[DIMAddFriend](@user nvarchar(256), @friend nvarchar(256))
as
begin
insert into DIMUserRelationship (HostKey,GuestKey,Relationship,RenewTime)
select host.[Key] as HostKey,guest.[Key] as GuestKey,0,getdate()
from DIMUsers host,DIMUsers guest
where
(host.UpperName=upper(@user) or host.UpperName=upper(@friend)) and
(guest.UpperName=upper(@friend) or guest.UpperName=upper(@user)) and
host.[Key]<>guest.[Key]
end
视图示例：
由于视图判断没有办法所以版本统一，所以写了2个版本。
2005以上版本：
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID=OBJECT_ID(N'VB_AccountPayableDetail') )
DROP VIEW VB_AccountPayableDetail
go
CREATE VIEW VB_AccountPayableDetail
AS
SELECT
A.COMPANY_ID,
A.ACCOUNT_PAYABLE_ID,
A.SNO,
A.MATERIAL_ID,
A.MATERIAL_NO,
A.MATERIAL_NAME,
A.MATERIAL_SPECIFICATION,
A.CURRENCY_ID,
EXCHANGE_RATE = CAST(A.EXCHANGE_RATE AS NUMERIC(18,2)),
A.UNIT_ID,
QUANTITY = CAST(A.QUANTITY AS NUMERIC(18,2)),
UNIT_PRICE = CAST(A.UNIT_PRICE AS NUMERIC(18,2)),
TAX_RATE = CAST(A.TAX_RATE AS NUMERIC(18,2)),
AMOUNT = CAST(A.AMOUNT AS NUMERIC(18,2)),
A.REMARK,
CURRENCY = B.NAME,
UNIT = C.NAME
FROM ACCOUNT_PAYABLE_ITEM A
LEFT JOIN S_CODELIB B
ON A.COMPANY_ID = B.COMPANY_ID
AND A.CURRENCY_ID = B.ID
LEFT JOIN S_CODELIB C
ON A.COMPANY_ID = C.COMPANY_ID
AND A.UNIT_ID = C.ID
2000版本：
IF EXISTS (SELECT * FROM sysviews WHERE OBJECT_ID=OBJECT_ID(N'VB_AccountPayableDetail') )
DROP VIEW VB_AccountPayableDetail
go
CREATE VIEW VB_AccountPayableDetail
AS
SELECT
A.COMPANY_ID,
A.ACCOUNT_PAYABLE_ID,
A.SNO,
A.MATERIAL_ID,
A.MATERIAL_NO,
A.MATERIAL_NAME,
A.MATERIAL_SPECIFICATION,
A.CURRENCY_ID,
EXCHANGE_RATE = CAST(A.EXCHANGE_RATE AS NUMERIC(18,2)),
A.UNIT_ID,
QUANTITY = CAST(A.QUANTITY AS NUMERIC(18,2)),
UNIT_PRICE = CAST(A.UNIT_PRICE AS NUMERIC(18,2)),
TAX_RATE = CAST(A.TAX_RATE AS NUMERIC(18,2)),
AMOUNT = CAST(A.AMOUNT AS NUMERIC(18,2)),
A.REMARK,
CURRENCY = B.NAME,
UNIT = C.NAME
FROM ACCOUNT_PAYABLE_ITEM A
LEFT JOIN S_CODELIB B
ON A.COMPANY_ID = B.COMPANY_ID
AND A.CURRENCY_ID = B.ID
LEFT JOIN S_CODELIB C
ON A.COMPANY_ID = C.COMPANY_ID
AND A.UNIT_ID = C.ID
索引示例：
if NOT exists(select * from sysindexes where name='IX_mis_AskForLeaveSheetFact_001')
BEGIN
CREATE NONCLUSTERED INDEX [IX_mis_AskForLeaveSheetFact_001] ON [dbo].[mis_AskForLeaveSheetFact]
(
[begin_date] ASC,
[begin_time] ASC
)
INCLUDE ( [Fact_leave_id],
[e_id],
[end_date],
[end_time],
[holidayNo]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
END
统计信息示例：
这个没有2000版本的只有2005版本
IF NOT EXISTS(SELECT NAME FROM sys.stats WHERE name='pms_exam_emp_001')
BEGIN
CREATE STATISTICS [pms_exam_emp_001] ON [dbo].[pms_exam_emp]([p_e_id], [e_type], [e_id])
END
这个统计信息是什么东东，这个玩意是跟索引配合的，在进行数据优化的时候很有用处。
以上脚本都要记住的就如果你要增加、修改、删除一个表，视图，函数，存储过程，索引，字段都必须判断是否存在。
5、关于脚本上传到svn的规范
1、文件命名规则：
A、每天建立一个文件夹
B、文件名称以设计的表名，字段名，函数，存储过程，日期时间，姓名
示例：doa_deletefield_BOM_alter_201109061105_wenjl.sql
存储过程名称+动作+时间+姓名
个人觉得加上时间比较好，这样可以有效避免存储过程函数等更改的先后问题。最好可以统一开发库。