1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
/*
* 创建时间：2011-10-09
* liud
* 使用目录:
*==============================================
* 01==查看表描述信息
*02==添加列（可多列）修改列删除列修改列名
* 03==删除表字段描述
*04==添加表字段描述（方法1、2）
* 05==修改表字段描述
* 06==修改列允许为空或不为空
*07==创建表主键外键索引修改主键
* 08==表列增加、修改默认值
* 09==函数Split
* 10==FOR XML AUTO
* 11==FOR XML PATH多种使用方式
* 12==将指定字符替换stuff('abc',2,1,'B') 结果：aBc
* 13==联合多表Update操作
* 14==日期函数
* 15==行列转换（两种方式）
* 16==删除/创建表建立的约束（如默认值）
* 17==With **AS（）使用
* 18==Update 时增加 @ERROR<>0OR @ROWCOUNT<>cnt(影响行数) 判断
* 19==大数据表循环删除脚本
*==============================================
*/
--111111111111111==============================查看表描述信息
SELECT*
FROM::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'CONTRACT', 'column', NULL)
--222222222222222==============================添加列（可多列）修改列删除列
ALTER TABLE 表 ADD 列 int,可多列逗号分隔
ALTER TABLE tableName ALTER column columnName varchar(4000)
ALTER TABLE tableName drop column columnName
EXECsp_rename'tableName.column1' , 'column2'--(把表名为tableName的column1列名修改为column2)
--33333333333333==============================删除表字段描述
--
EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','CONTRACT',null,null
EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','CONTRACT','column',BrandId
--4444444444444==============================添加表字段描述（方法1、2）
--表描述
EXEC sp_addextendedproperty N'MS_Description', '添加表描述', N'user', N'dbo', N'table', N'表', NULL, NULL
--方法1
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加字段描述1'
, @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表', @level2type=N'COLUMN',@level2name=N'列'
GO
--方法2
EXECUTE sp_addextendedproperty N'MS_Description', '添加字段描述2', N'user', N'dbo', N'table', N'表', N'column', N'列'
GO
--5555555555555==============================修改表字段描述信息
--
EXEC sp_updateextendedproperty 'MS_Description','修改表描述',N'user', N'dbo', N'table','表',null,null
EXEC sp_updateextendedproperty 'MS_Description','修改字段描述',N'user', N'dbo', N'table','表','column',a1
--666666666666666==============================修改列允许为空或不为空
--
--允许为空
alter table 表 ALTER COLUMN 列 bit NULL
alter table 表 ALTER COLUMN 列 BIT NOT NULL
--77777777777777==============================创建表主键索引
--
--检查索引是否存在，存在则删除
if exists (select 1
fromsysindexes
whereid= object_id('dbo.SCCLWayBill')
andname= 'IX_SCCLWayBill_DispatchNo'
andindid > 0
andindid < 255)
drop index dbo.SCCLWayBill.IX_SCCLWayBill_DispatchNo
GO
--创建主键
CREATE TABLE tbname(
id INT NOT NULL,--不为空
NAME NVARCHAR(20) NULL,--可为空
STATUS INT DEFAULT 0,--默认值
constraint PK_tbname primary key (id),--主键
waijian int foreign key(waijian) references A_tablename(AID)--外键
)
--修改主键
--1首先删除主键
ALTERTABLE [tbname] DROPCONSTRAINT [PK_tbname]
--2创建主键
ALTERTABLE [tbname] ADDCONSTRAINT [PK_tbname] PRIMARYKEY
(
column1 ASC,
column2 ASC,
column3 ASC
)
GO
--创建索引
--聚集索引
create index IX_tbname_id on dbo.tbname (
id ASC
)
go
--非聚集索引
CREATE nonclusteredindex IX_tbname_id on dbo.tbname (
id ASC
)
go
--8888888888888==============================表列增加默认值
ALTER TABLE [tbName] ADDCONSTRAINT [约束名(自定义)]DEFAULT ((2)) FOR [ColumnName]
ALTER TABLE [tbName] ADDDEFAULT ((1)) FOR [column]
--如果需要修改默认值
ALTER TABLE [tbName] DROP CONSTRAINT [约束名(自定义)]--删除约束 然后再新增
--
--999999999999999============================Split函数
CREATE Function [Split](@Sql varchar(8000),@Splits varchar(10))
returns @temp Table (a varchar(100))
As
Begin
Declare @i Int
Set @Sql = RTrim(LTrim(@Sql))
Set @i = CharIndex(@Splits,@Sql)
While @i >= 1
Begin
Insert @temp Values(Left(@Sql,@i-1))
Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i)
Set @i = CharIndex(@Splits,@Sql)
End
If @Sql <> ''
Insert @temp Values (@Sql)
Return
End
--测试
--SELECT * FROMdbo.Split('L.I.U.D.O.N.G','.')
-------------------10=========================FOR XMLAUTO
DECLARE @temT TABLE(ID INT,NAME NVARCHAR(50))
INSERT INTO @temT VALUEs(1,'liudong');
INSERT INTO @temT VALUEs(2,'shichunjie');
SELECT * FROM @temT FOR XML AUTO
-------------------11=========================FOR XMLPATH
SELECT * FROM @temT FOR XML PATH
SELECT * FROM @temT FOR XML PATH('T')
SELECT * FROM @temT FOR XML PATH('')
SELECT cast(id AS NVARCHAR(10))+',',NAME+'.' FROM @temT FOR XML PATH('')
-------------------12=========================stuff('abc',2,1,'B')
select stuff('abc',2,1,'B') --结果 aBc
--
-------------------13=========================联合多表Update操作
--
UPDATE t1 SET tclo=1 FROM t1,t2WHERE t1.id=t2.id
-------------------14=========================日期函数
--
--datediff(ex,begin,end)
--时差--ex:表达式，begin:开始时间，end:结束时间，ex=end-begin
SELECT DATEDIFF(hh,'2012-03-03 10:11','2012-03-04 09:11')
--加时--ex:表达式，加时，时间
SELECT DATEADD(hh,24,'2012-03-03 10:11')
-------------------15=========================行列互转（两种方式）
--1 定义变量
DECLARE @result VARCHAR(255)
SET @result = ''
SELECT@result = @result + cast(tdd.[name] as varchar(255)) +','
FROM T tdd(NOLOCK) WHERE1=1
IF(len(@result) - 1<0) SELECT '' AS [name]
ELSESELECTLEFT(@result,len(@result) - 1)AS [name]
--2 使用 for xmlpath(''),使用stuff函数将第一个,号替换掉
SELECT
[name]=stuff((select ','+cast(tt.[name] AS VARCHAR(10))
from T tt where 1=1 for xml path('')), 1, 1, '')
FROM @temp
-------------------16=========================查看/删除/创建表约束
--查看表约束
sp_helpconstraint表名
--删除表约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名
--创建表约束
ALTER TABLE 表名 ADDCONSTRAINT 约束名DEFAULT ('') FOR 字段
-------------------17===========================With **AS（）使用
--- 临时表
WITH tt AS (
SELECT * FROM tab
)
-- 递归
WITH dept AS(
SELECT cd.Code,cd.CodeName
FROM CostDept cd(NOLOCK) WHERE cd.Code='0123'
UNION ALL
SELECT cd1.Code,cd1.CodeName
FROM dept JOIN CostDept cd1(NOLOCK) ON dept.Code=cd1.ParentCode
)
SELECT * FROM dept
-------------------------------------------------------------------------------
SELECT tt.DeliveryOrderNO,tt.SysOrderTypeFROM (
SELECT T.DeliveryOrderNO,
SysOrderType=stuff((select ','+cast(tt.SysOrderType AS VARCHAR(10))
from TMS_DeliveryOrderDetail tt where tt.DeliveryOrderNO=T.DeliveryOrderNOfor xml path('')), 1, 1, '')
FROM (
SELECT tdo.DeliveryOrderNO
FROM TMS_DeliveryOrder tdo
WHERE 1=1
and tdo.ReceivedDate>='2012-03-01 01:01'AND tdo.ReceivedDate<=GETDATE()
AND tdo.OrderType=2
--ANDtdo.DeliveryOrderNO='0103Y1203010001'
) T ) TT WHERE1=1
-- and(charindex('13',SysOrderType)>0 OR charindex('13',SysOrderType)>0 ORcharindex('16',SysOrderType)>0 OR charindex('17',SysOrderType)>0 ORcharindex('18',SysOrderType)>0)
--ANDcharindex('13',SysOrderType)=0 AND charindex('16',SysOrderType)=0 ANDcharindex('18',SysOrderType)=0
AND (charindex('10',SysOrderType)>0 OR charindex('17',SysOrderType)>0)
AND (charindex('13',SysOrderType)=0 AND charindex('16',SysOrderType)=0AND charindex('18',SysOrderType)=0)
SELECT * FROM TMS_DeliveryOrderDetail tdod WHERE tdod.FactoryID IS NOT NULL
-------------------18================Update 时增加@ERROR<>0 OR @ROWCOUNT<>cnt(影响行数) 判断
IF(@@ERROR<>0 OR @@ROWCOUNT<>1)
BEGIN
ROLLBACK
PRINT 'RollBack'
RETURN
END
PRINT 'Commit Start'
COMMIT
PRINT 'Commit Over'
SELECT * FROM TMS_DeliveryOrderDetail tdod
WHERE EXISTS(
SELECT * FROM TMS_SYN_Redeploy tsr WHERE
tsr.RedeployCode IN
(
)
--------------------19=====================大数据表循环删除脚本
DECLARE @icount INTEGER;
DECLARE @StartDate date;
DECLARE @EndDate date;
SET @StartDate = CONVERT(date,'2012-08-01');
SET @EndDate = CONVERT(date,'2012-08-31');
;
WHILE 1=1
BEGIN
DELETE TOP (20000)
FROM [dbo].[ForecastBaseData]
WHERE CreateDate>=@StartDate
and CreateDate<=@EndDate
;
SET @icount = @@ROWCOUNT
;
IF @icount<>20000
BREAK
;
WAITFOR DELAY '00:00:05'
END
--------------------19END=====================
