SQLSERVER 使用SQL操作字段、索引

添加字段

alter table table_name add column_name nvarchar(10)

设置字段为主键

alter table table_name add primary key(column_name)

修改字段类型

将column_name字段类型变更为 nvarchar(10)

alter table table_name alter column column_name nvarchar(10)

修改字段名称

execute sp_rename 'table_name.column_name','new_column_name'

修改字段不能为Null值

alter table table_name alter column clolumn_name nvarchar(10) not null

字段添加注释

EXEC sp_addextendedproperty N'MS_Description', N'此处为需要添加的注释信息'-- 注释
, N'SCHEMA', N'dbo',N'TABLE', N'table_name'--表名, N'COLUMN', N'column_name';--字段名

删除字段

alter table table_name drop column column_name

创建普通索引

create index index_name on table_name(column_name)

创建唯一索引

create unique index index_name on table_name(column_name)

创建组合索引

create index index_name on table_name(column_name1,column_name2)

查看表名称和对应的描述

SELECT tbs.name 表名,ds.value 描述   
FROM sys.extended_properties ds  
LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id  
WHERE  ds.minor_id=0 and  
 tbs.name='table_name';--表名  

查看表结构

SELECT  CASE WHEN col.colorder = 1 THEN obj.name 

                  ELSE '' 

             END AS 表名, 

        col.colorder AS 序号 , 

        col.name AS 列名 , 

        ISNULL(ep.[value], '') AS 列说明 , 

        t.name AS 数据类型 , 

        col.length AS 长度 , 

        ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 , 

        CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√' 

             ELSE '' 

        END AS 标识 , 

        CASE WHEN EXISTS ( SELECT   1 

                           FROM     dbo.sysindexes si 

                                    INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id 

                                                              AND si.indid = sik.indid 

                                    INNER JOIN dbo.syscolumns sc ON sc.id = sik.id 

                                                              AND sc.colid = sik.colid 

                                    INNER JOIN dbo.sysobjects so ON so.name = si.name 

                                                              AND so.xtype = 'PK' 

                           WHERE    sc.id = col.id 

                                    AND sc.colid = col.colid ) THEN '√' 

             ELSE '' 

        END AS 主键 , 

        CASE WHEN col.isnullable = 1 THEN '√' 

             ELSE '' 

        END AS 允许空 , 

        ISNULL(comm.text, '') AS 默认值 

FROM    dbo.syscolumns col 

        LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype 

        inner JOIN dbo.sysobjects obj ON col.id = obj.id 

                                         AND obj.xtype = 'U' 

                                         AND obj.status >= 0 

        LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id 

        LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id 

                                                      AND col.colid = ep.minor_id 

                                                      AND ep.name = 'MS_Description' 

        LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id 

                                                         AND epTwo.minor_id = 0 

                                                         AND epTwo.name = 'MS_Description' 

WHERE   obj.name = 'table_name'--表名 

ORDER BY col.colorder ;