SQL

1,sql server 查询表信息

SELECT 
        '表名'        = e.[name],
        '表说明'      = f.[value],
        '字段序号'    = a.colorder,
        '字段名'      = a.[name],
        '字段类型'    = b.[name],
        '字段长度'    = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
        '占用字节数'  = a.length,
        '字段注释'    = c.[value],
        '小数位数'    = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
        '是否允许空'  = case when a.isnullable=1 then '√'else '' end,
        '默认值'      = d.[text],
        '是否是主键'        = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                           SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
LEFT JOIN sys.extended_properties c ON a.id=c.major_id AND a.colid=c.minor_id
left join syscomments d on a.cdefault=d.id
left join sysobjects e on a.id=e.id  and e.xtype='U' and  e.[name]<>'dtproperties'
left JOIN sys.extended_properties f on e.id=f.major_id and f.minor_id=0 --AND f.[name]='MS_Description'--f.name为属性名字,一个表可以有多个属性，表的属性名字右键新建
WHERE a.id=OBJECT_ID('dbo.Member')

 2，sql 将一张表中的数据插入到另一张表

insert into t_wz_kc(wzid,jldwid,kcsl,yfpkcsl,cshwcbz) select wzid,jldwid,0,0,'Y' from t_wz_wz where yxbz='Y' 

--去重复
-- and wzid not in (select wzid from t_wz_kc) 

3，修改表结构

修改字段名：
alter table tab_info rename column createname to thisname;

修改字段属性：
alter table tab_info alter column thisname varchar(200) not null;

修改默认值
alter table tabinfo add constraint df default('嘿嘿') for thisname;

增加列
alter table [TableName] add [P_ID] bigint not null default 0

删除列
alter table [TableName] drop column [P_ID]

设置主键
alter table [TableName] add constraint PK_TableName primary key (P_ID)

删除主键
alter table [TableName] drop constraint PK_TableName

增加字段说明
EXECUTE sp_addextendedproperty N'MS_Description', N'照片ID', N'user', N'dbo', N'table', N'TableName', N'column', N'P_ID' 

 4，表索引

查看索引
sp_helpindex PDKeywordIndex

删除索引

drop index  PDKeywordIndex.PDKeywordIndex_Keyword

创建聚集索引

  create clustered index PDKeywordIndex_Keyword
  on PDKeywordIndex(Keyword)

 

  创建非聚集索引

 

  create nonclustered index PDKeywordIndex_Keyword
  on PDKeywordIndex(Keyword)