sql 神器，再也不用记东记西了 O(∩_∩)O

/*
* 创建时间：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<>0 OR @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 
EXEC  sp_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
            from  sysindexes
           where  id    = object_id('dbo.SCCLWayBill')
            and   name  = 'IX_SCCLWayBill_DispatchNo'
            and   indid > 0
            and   indid < 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首先删除主键
ALTER  TABLE [tbname] DROP  CONSTRAINT [PK_tbname]
--2创建主键
ALTER  TABLE [tbname] ADD   CONSTRAINT [PK_tbname] PRIMARY   KEY  
          ( 
                    column1 ASC,
					column2 ASC,
					column3 ASC
          ) 
        GO

--创建索引
--聚集索引
create index IX_tbname_id on dbo.tbname (
id ASC
)
go
--非聚集索引
CREATE nonclustered  index IX_tbname_id on dbo.tbname (
id ASC
)
go

--8888888888888==============================表列增加默认值
ALTER TABLE [tbName] ADD  CONSTRAINT [约束名(自定义)]  DEFAULT ((2)) FOR [ColumnName]
ALTER TABLE [tbName] ADD  DEFAULT ((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 * FROM  dbo.Split('L.I.U.D.O.N.G','.')
-------------------10=========================FOR XML AUTO
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 XML PATH
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,t2  WHERE 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) WHERE   1=1
IF(len(@result) - 1<0) SELECT '' AS [name]
ELSE  SELECT  LEFT(@result,len(@result) - 1)  AS [name]

--2 使用 for xml path(''),使用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 表名 ADD  CONSTRAINT 约束名  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.SysOrderType FROM (
 SELECT T.DeliveryOrderNO,
 SysOrderType=stuff((select ','+cast(tt.SysOrderType AS VARCHAR(10))
                     from TMS_DeliveryOrderDetail tt where tt.DeliveryOrderNO=T.DeliveryOrderNO for 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  
 --AND tdo.DeliveryOrderNO='0103Y1203010001'
 ) T ) TT WHERE  1=1
 -- and (charindex('13',SysOrderType)>0 OR charindex('13',SysOrderType)>0 OR charindex('16',SysOrderType)>0 OR charindex('17',SysOrderType)>0 OR charindex('18',SysOrderType)>0)
 --AND charindex('13',SysOrderType)=0 AND charindex('16',SysOrderType)=0 AND charindex('18',SysOrderType)=0
 AND (charindex('10',SysOrderType)>0 OR charindex('17',SysOrderType)>0)
 AND (charindex('13',SysOrderType)=0 AND charindex('16',SysOrderType)=0  AND 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
 --------------------19 END=====================