/** 创建时间：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* ==============================================*/--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,nullEXEC 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 --方法1EXEC 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--方法2EXECUTE sp_addextendedproperty N'MS_Description', '添加字段描述2', N'user', N'dbo', N'table', N'表', N'column', N'列' GO--5555555555555==============================修改表\字段描述信息--EXEC sp_updateextendedproperty 'MS_Description','修改表描述','user',dbo,'table','表',null,nullEXEC sp_updateextendedproperty 'MS_Description','修改字段描述','user',dbo,'table','表','column',a1 --666666666666666==============================修改列允许为空或不为空----允许为空alter table 表 ALTER COLUMN 列 bit NULLalter 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_DispatchNoGO--创建主键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)--外键)--创建索引--create index IX_tbname_id on dbo.tbname (id ASC)go--8888888888888==============================表列增加默认值ALTER TABLE [tbName] ADD CONSTRAINT [约束名(自定义)] DEFAULT ((2)) FOR [ColumnName]--如果需要修改默认值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 AUTODECLARE @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 PATHSELECT * FROM @temT FOR XML PATHSELECT * 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')
