SQLスクリプトの作成提案について
18525 ワード
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
+ + +
， 。 。