中文转成拼音首字母

CREATE   function   GetAllPY(@str   nvarchar(4000)) 
returns   nvarchar(4000) 
   
--WITH   ENCRYPTION 
   
as 
begin 
declare   @intLen int 
declare   @strRet nvarchar(4000) 
declare   @temp   nvarchar(100) 
   
set   @intLen   =   len(@str) 
set   @strRet   =   '' 
   
while   @intLen   >   0 
begin 
set   @temp   =   '' 
   
select   @temp   =   case    
when   substring(@str,@intLen,1)   >=   '帀'   then   'Z' 
when   substring(@str,@intLen,1)   >=   '丫'   then   'Y' 
when   substring(@str,@intLen,1)   >=   '夕'   then   'X' 
when   substring(@str,@intLen,1)   >=   '屲'   then   'W' 
when   substring(@str,@intLen,1)   >=   '他'   then   'T' 
when   substring(@str,@intLen,1)   >=   '仨'   then   'S' 
when   substring(@str,@intLen,1)   >=   '呥'   then   'R' 
when   substring(@str,@intLen,1)   >=   '七'   then   'Q' 
when   substring(@str,@intLen,1)   >=   '妑'   then   'P' 
when   substring(@str,@intLen,1)   >=   '噢'   then   'O' 
when   substring(@str,@intLen,1)   >=   '拏'   then   'N' 
when   substring(@str,@intLen,1)   >=   '嘸'   then   'M' 
when   substring(@str,@intLen,1)   >=   '垃'   then   'L' 
when   substring(@str,@intLen,1)   >=   '咔'   then   'K' 
when   substring(@str,@intLen,1)   >=   '丌'   then     'J' 
when   substring(@str,@intLen,1)   >=   '铪'   then   'H' 
when   substring(@str,@intLen,1)   >=   '旮'   then   'G' 
when   substring(@str,@intLen,1)   >=   '发'   then   'F' 
when   substring(@str,@intLen,1)   >=   '妸'   then   'E' 
when   substring(@str,@intLen,1)   >=   '咑'   then   'D' 
when   substring(@str,@intLen,1)   >=   '嚓'   then   'C' 
when   substring(@str,@intLen,1)   >=   '八'   then   'B' 
when   substring(@str,@intLen,1)   >=   '吖'   then   'A' 
else   rtrim(ltrim(substring(@str,@intLen,1))) 
end 
   
--对于汉字特殊字符，不生成拼音码 
if   (ascii(@temp)>127)   set   @temp   =   '' 
   
--对于英文中小括号，不生成拼音码 
if   @temp   =   '('   or   @temp   =   ')'   set   @temp   =   '' 
   
select   @strRet   =   @temp   +   @strRet 
   
set   @intLen   =   @intLen   -   1 
end 
   
return   lower(@strRet) 
end  

-------------------------------------------------
取得所有表的表名、字段名称、标示、说明等

SELECT 
    (case when a.colorder=1 then d.name else '' end) 表名,
    a.colorder 字段序号,
    a.name 字段名,dbo.GetAllPY(a.name),
    (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
    (case when (SELECT count(*) 
                FROM sysobjects 
                WHERE (name in (SELECT name
                                FROM sysindexes
                                WHERE (id = a.id) AND (indid in (SELECT indid
                                                                 FROM sysindexkeys
                                                                 WHERE (id = a.id) AND (colid in (SELECT colid
                                                                                                  FROM syscolumns
                                                                                                  WHERE (id = a.id) AND (name = a.name)
                                                                                                  )
                                                                                        )
                                                                )
                                                        )
                                )
                        ) AND (xtype = 'PK') 
                ) > 0 then '√' else '' end) 主键,
    b.name 类型,
    a.length 占用字节数,
    COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
    isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
    (case when a.isnullable=1 then '√'else '' end) 允许空,
    isnull(e.text,'') 默认值,
    isnull(g.[value],a.name) AS 字段说明   

FROM syscolumns a 
    left join systypes b on a.xtype=b.xusertype
    inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
    left join syscomments e on a.cdefault=e.id
    left join sysproperties g on a.id=g.id AND a.colid = g.smallid

order by a.id,a.colorder

ms sql2005下

SELECT 
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = 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,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
FROM 
    syscolumns a
left join 
    systypes b 
on 
    a.xusertype=b.xusertype
inner join 
    sysobjects d 
on 
    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
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='lj_pruchase_material'    --如果只查询指定表,加上此条件
order by 
    a.id,a.colorder