SQLSERVER使用小记

生成数据字典

select 'CE' as 数据库
  , d.name as 表名
  , f.value as 表说明
  , a.colorder as 字段序号
  , a.name as 字段名
  , case when b.name='text' then b.name
         when coalesce(columnproperty(a.id,a.name,'Scale'),0)=0 then 
             b.name+'('+convert(varchar(20),columnproperty(a.id,a.name,'PRECISION'))+')'
    else b.name+'('+convert(varchar(20),columnproperty(a.id,a.name,'PRECISION'))+','
              +convert(varchar(20),coalesce(columnproperty(a.id,a.name,'Scale'),0))+')' end 类型
  , case when a.isnullable=1 then '' else 'Y' end as 不允许空
  , e.text as 默认值
  , case when exists(select 1 from sysobjects where xtype='PK' and name in (
      select name from sysindexes where indid in(
        select indid from sysindexkeys where id = a.id and colid=a.colid
      )
    )) then 'PRI' else '' end as 键值
  , g.value as 字段说明
from syscolumns a
left join systypes b on a.xtype=b.xusertype
join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
-- left join sysindexes AS i ON d.id = i.id and indid=1
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0
-- where d.name='dm_ce_customer_experience_detail' --如果只查询指定表,加上此条件
order by d.name,a.colorder
;

日期格式化函数

select convert(varchar(100), getdate(), 0) -- 05 16 2006 10:57AM
select convert(varchar(100), getdate(), 1) -- 05/16/06
select convert(varchar(100), getdate(), 2) -- 06.05.16
select convert(varchar(100), getdate(), 3) -- 16/05/06
select convert(varchar(100), getdate(), 4) -- 16.05.06
select convert(varchar(100), getdate(), 5) -- 16-05-06
select convert(varchar(100), getdate(), 6) -- 16 05 06
select convert(varchar(100), getdate(), 7) -- 05 16, 06
select convert(varchar(100), getdate(), 8) -- 10:57:46
select convert(varchar(100), getdate(), 9) -- 05 16 2006 10:57:46:827AM
select convert(varchar(100), getdate(), 10) -- 05-16-06
select convert(varchar(100), getdate(), 11) -- 06/05/16
select convert(varchar(100), getdate(), 12) -- 060516
select convert(varchar(100), getdate(), 13) -- 16 05 2006 10:57:46:937
select convert(varchar(100), getdate(), 14) -- 10:57:46:967
select convert(varchar(100), getdate(), 20) -- 2006-05-16 10:57:47
select convert(varchar(100), getdate(), 21) -- 2006-05-16 10:57:47.157
select convert(varchar(100), getdate(), 22) -- 05/16/06 10:57:47 AM
select convert(varchar(100), getdate(), 23) -- 2006-05-16
select convert(varchar(100), getdate(), 24) -- 10:57:47
select convert(varchar(100), getdate(), 25) -- 2006-05-16 10:57:47.250
select convert(varchar(100), getdate(), 100) -- 05 16 2006 10:57AM
select convert(varchar(100), getdate(), 101) -- 05/16/2006
select convert(varchar(100), getdate(), 102) -- 2006.05.16
select convert(varchar(100), getdate(), 103) -- 16/05/2006
select convert(varchar(100), getdate(), 104) -- 16.05.2006
select convert(varchar(100), getdate(), 105) -- 16-05-2006
select convert(varchar(100), getdate(), 106) -- 16 05 2006
select convert(varchar(100), getdate(), 107) -- 05 16, 2006
select convert(varchar(100), getdate(), 108) -- 10:57:49
select convert(varchar(100), getdate(), 109) -- 05 16 2006 10:57:49:437AM
select convert(varchar(100), getdate(), 110) -- 05-16-2006
select convert(varchar(100), getdate(), 111) -- 2006/05/16
select convert(varchar(100), getdate(), 112) -- 20060516
select convert(varchar(100), getdate(), 113) -- 16 05 2006 10:57:49:513
select convert(varchar(100), getdate(), 114) -- 10:57:49:547
select convert(varchar(100), getdate(), 120) -- 2006-05-16 10:57:49
select convert(varchar(100), getdate(), 121) -- 2006-05-16 10:57:49.700
select convert(varchar(100), getdate(), 126) -- 2006-05-16T10:57:49.827
select convert(varchar(100), getdate(), 130) -- 18 ???? ?????? 1427 10:57:49:907AM
select convert(varchar(100), getdate(), 131) -- 18/04/1427 10:57:49:920AM