---------------------
--创建通用选项表
/****** Object: Table [dbo].[sysComOption] Script Date: 01/08/2017 14:08:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[sysComOption]( [ID] [int] IDENTITY(1,1) NOT NULL, [Code] [nvarchar](20) NOT NULL, [ParentCode] [nvarchar](20) NULL, [Version] [int] NOT NULL, [Status] [int] NOT NULL, [OptionType] [nvarchar](20) NULL, [OptionTag] [nvarchar](150) NULL, [OtherTag] [xml] NULL, [OptionName] [nvarchar](50) NOT NULL, [OptionCNName] [nvarchar](50) NOT NULL, [OptionDisplayName] [nvarchar](50) NULL, [OptionAbbPYName] [nvarchar](20) NULL, [OptionFullPYName] [nvarchar](150) NULL, [OptionValueType] [int] NOT NULL, [OptionDisplayValue] [nvarchar](50) NULL, [OptionValueInt] [int] NULL, [OptionValueNvarchar] [nvarchar](150) NULL, [OptionValueFloat] [float] NULL, [OptionValueNum] [numeric](18, 0) NULL, [Comments] [nvarchar](500) NULL, [Create_By] [int] NULL, [Create_date] [datetime] NULL, [lastmaintenance_by] [int] NULL, [Lastmodify_date] [datetime] NULL, CONSTRAINT [PK_sysComOption] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 int;5 nvarchat; 10 float;15 Num' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sysComOption', @level2type=N'COLUMN',@level2name=N'OptionValueType' GO
--创建区县初始化数据表
--创建区县数据初始化表 /****** Object: Table [dbo].[regionTemp] Script Date: 01/08/2017 14:09:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[regionTemp]( [code] [float] NULL, [parentcode] [varchar](10) NULL, [parentRegionname] [nvarchar](255) NULL, [Regionname] [nvarchar](255) NULL, [GPS] [varchar](46) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
code parentcode parentRegionname Regionname GPS
110000 110000 北京市 北京市 GPS(39.9081726,116.3979471)
110101 110000 北京市 东城区 GPS(39.9351199,116.4093947)
110102 110000 北京市 西城区 GPS(39.9351775,116.3638616)
110103 110000 北京市 崇文区 GPS(39.8882042,116.4318180)
110104 110000 北京市 宣武区 GPS(39.8771968,116.3530469)
110105 110000 北京市 朝阳区 GPS(39.9527472,116.4977252)
110106 110000 北京市 丰台区 GPS(39.8307687,116.2682462)
-----------------------------------------------
创建拼音缩写（全拼转换 & 将汉字转换首个拼音大写字母）
/****** Object: UserDefinedFunction [dbo].[fn_GetFullPinyin] Script Date: 01/08/2017 14:27:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE function [dbo].[fn_GetFullPinyin](@words nVARCHAR(2000),@withblank int) --@withblank确定每个中文字转为拼音后，每个汉字之间是否需要留下空格0为不留，1为留 RETURNS VARCHAR(8000) AS BEGIN DECLARE @word nchar(1) DECLARE @paceword nvarchar(1) DECLARE @pinyin VARCHAR(8000) DECLARE @i INT DECLARE @words_len INT DECLARE @unicode INT SET @i = 1 SET @words =ltrim(rtrim(@words)) SET @words_len =len(@words) WHILE(@i <= @words_len)--循环取字符 BEGIN SET @word =substring(@words, @i, 1) SET @unicode =unicode(@word) if (@withblank=0) begin set @paceword ='' end else if(@withblank=1) begin set @paceword =SPACE(1) end SET @pinyin =ISNULL(@pinyin +@paceword ,'')+ (CASE WHEN unicode(@word)between 19968 and 19968+20901 then (SELECT TOP 1 py FROM ( SELECT'a'AS py,N'厑'AS word UNION ALL SELECT 'ai',N'靉' UNION ALL SELECT'an',N'黯' UNION ALL SELECT'ang',N'醠' UNION ALL SELECT'ao',N'驁' UNION ALL SELECT'ba',N'欛' UNION ALL SELECT'bai',N'瓸'--韛兡瓸 UNION ALL SELECT'ban',N'瓣' UNION ALL SELECT'bang',N'鎊' UNION ALL SELECT'bao',N'鑤' UNION ALL SELECT'bei',N'鐾' UNION ALL SELECT'ben',N'輽' UNION ALL SELECT'beng',N'鏰' UNION ALL SELECT'bi',N'鼊' UNION ALL SELECT'bian',N'變' UNION ALL SELECT'biao',N'鰾' UNION ALL SELECT'bie',N'彆' UNION ALL SELECT'bin',N'鬢' UNION ALL SELECT'bing',N'靐' UNION ALL SELECT'bo',N'蔔' UNION ALL SELECT'bu',N'簿' UNION ALL SELECT'ca',N'囃' UNION ALL SELECT'cai',N'乲'--縩乲 UNION ALL SELECT'can',N'爘' UNION ALL SELECT'cang',N'賶' UNION ALL SELECT'cao',N'鼜' UNION ALL SELECT'ce',N'簎' UNION ALL SELECT'cen',N'笒' UNION ALL SELECT'ceng',N'乽'--硛硳岾猠乽 UNION ALL SELECT'cha',N'詫' UNION ALL SELECT'chai',N'囆' UNION ALL SELECT'chan',N'顫' UNION ALL SELECT'chang',N'韔' UNION ALL SELECT'chao',N'觘' UNION ALL SELECT'che',N'爡' UNION ALL SELECT'chen',N'讖' UNION ALL SELECT'cheng',N'秤' UNION ALL SELECT'chi',N'鷘' UNION ALL SELECT'chong',N'銃' UNION ALL SELECT'chou',N'殠' UNION ALL SELECT'chu',N'矗' UNION ALL SELECT'chuai',N'踹' UNION ALL SELECT'chuan',N'鶨' UNION ALL SELECT'chuang',N'愴' UNION ALL SELECT'chui',N'顀' UNION ALL SELECT'chun',N'蠢' UNION ALL SELECT'chuo',N'縒' UNION ALL SELECT'ci',N'嗭'--賜嗭 UNION ALL SELECT'cong',N'謥' UNION ALL SELECT'cou',N'輳' UNION ALL SELECT'cu',N'顣' UNION ALL SELECT'cuan',N'爨' UNION ALL SELECT'cui',N'臎' UNION ALL SELECT'cun',N'籿' UNION ALL SELECT'cuo',N'錯' UNION ALL SELECT'da',N'橽' UNION ALL SELECT'dai',N'靆' UNION ALL SELECT'dan',N'饏' UNION ALL SELECT'dang',N'闣' UNION ALL SELECT'dao',N'纛' UNION ALL SELECT'de',N'的' UNION ALL SELECT'den',N'扽' UNION ALL SELECT'deng',N'鐙' UNION ALL SELECT'di',N'螮' UNION ALL SELECT'dia',N'嗲' UNION ALL SELECT'dian',N'驔' UNION ALL SELECT'diao',N'鑃' UNION ALL SELECT'die',N'嚸'--眰嚸 UNION ALL SELECT'ding',N'顁' UNION ALL SELECT'diu',N'銩' UNION ALL SELECT'dong',N'霘' UNION ALL SELECT'dou',N'鬭' UNION ALL SELECT'du',N'蠹' UNION ALL SELECT'duan',N'叾'--籪叾 UNION ALL SELECT'dui',N'譵' UNION ALL SELECT'dun',N'踲' UNION ALL SELECT'duo',N'鵽' UNION ALL SELECT'e',N'鱷' UNION ALL SELECT'en',N'摁' UNION ALL SELECT'eng',N'鞥' UNION ALL SELECT'er',N'樲' UNION ALL SELECT'fa',N'髮' UNION ALL SELECT'fan',N'瀪' UNION ALL SELECT'fang',N'放' UNION ALL SELECT'fei',N'靅' UNION ALL SELECT'fen',N'鱝' UNION ALL SELECT'feng',N'覅' UNION ALL SELECT'fo',N'梻' UNION ALL SELECT'fou',N'鴀' UNION ALL SELECT'fu',N'猤'--鰒猤 UNION ALL SELECT'ga',N'魀' UNION ALL SELECT'gai',N'瓂' UNION ALL SELECT'gan',N'灨' UNION ALL SELECT'gang',N'戇' UNION ALL SELECT'gao',N'鋯' UNION ALL SELECT'ge',N'獦' UNION ALL SELECT'gei',N'給' UNION ALL SELECT'gen',N'搄' UNION ALL SELECT'geng',N'堩'--亙堩啹喼嗰 UNION ALL SELECT'gong',N'兣'--熕贑兝兣 UNION ALL SELECT'gou',N'購' UNION ALL SELECT'gu',N'顧' UNION ALL SELECT'gua',N'詿' UNION ALL SELECT'guai',N'恠' UNION ALL SELECT'guan',N'鱹' UNION ALL SELECT'guang',N'撗' UNION ALL SELECT'gui',N'鱥' UNION ALL SELECT'gun',N'謴' UNION ALL SELECT'guo',N'腂' UNION ALL SELECT'ha',N'哈' UNION ALL SELECT'hai',N'饚' UNION ALL SELECT'han',N'鶾' UNION ALL SELECT'hang',N'沆' UNION ALL SELECT'hao',N'兞' UNION ALL SELECT'he',N'靏' UNION ALL SELECT'hei',N'嬒' UNION ALL SELECT'hen',N'恨' UNION ALL SELECT'heng',N'堼'--堼囍 UNION ALL SELECT'hong',N'鬨' UNION ALL SELECT'hou',N'鱟' UNION ALL SELECT'hu',N'鸌' UNION ALL SELECT'hua',N'蘳' UNION ALL SELECT'huai',N'蘾' UNION ALL SELECT'huan',N'鰀' UNION ALL SELECT'huang',N'鎤' UNION ALL SELECT'hui',N'顪' UNION ALL SELECT'hun',N'諢' UNION ALL SELECT'huo',N'夻' UNION ALL SELECT'ji',N'驥' UNION ALL SELECT'jia',N'嗧' UNION ALL SELECT'jian',N'鑳' UNION ALL SELECT'jiang',N'謽' UNION ALL SELECT'jiao',N'釂' UNION ALL SELECT'jie',N'繲' UNION ALL SELECT'jin',N'齽' UNION ALL SELECT'jing',N'竸' UNION ALL SELECT'jiong',N'蘔' UNION ALL SELECT'jiu',N'欍' UNION ALL SELECT'ju',N'爠' UNION ALL SELECT'juan',N'羂' UNION ALL SELECT'jue',N'钁' UNION ALL SELECT'jun',N'攈' UNION ALL SELECT'ka',N'鉲' UNION ALL SELECT'kai',N'乫'--鎎乫 UNION ALL SELECT'kan',N'矙' UNION ALL SELECT'kang',N'閌' UNION ALL SELECT'kao',N'鯌' UNION ALL SELECT'ke',N'騍' UNION ALL SELECT'ken',N'褃' UNION ALL SELECT'keng',N'鏗'--巪乬唟厼怾 UNION ALL SELECT'kong',N'廤' UNION ALL SELECT'kou',N'鷇' UNION ALL SELECT'ku',N'嚳' UNION ALL SELECT'kua',N'骻' UNION ALL SELECT'kuai',N'鱠' UNION ALL SELECT'kuan',N'窾' UNION ALL SELECT'kuang',N'鑛' UNION ALL SELECT'kui',N'鑎' UNION ALL SELECT'kun',N'睏' UNION ALL SELECT'kuo',N'穒' UNION ALL SELECT'la',N'鞡' UNION ALL SELECT'lai',N'籟' UNION ALL SELECT'lan',N'糷' UNION ALL SELECT'lang',N'唥' UNION ALL SELECT'lao',N'軂' UNION ALL SELECT'le',N'餎' UNION ALL SELECT'lei',N'脷'--嘞脷 UNION ALL SELECT'leng',N'睖' UNION ALL SELECT'li',N'瓈' UNION ALL SELECT'lia',N'倆' UNION ALL SELECT'lian',N'纞' UNION ALL SELECT'liang',N'鍄' UNION ALL SELECT'liao',N'瞭' UNION ALL SELECT'lie',N'鱲' UNION ALL SELECT'lin',N'轥'--轥拎 UNION ALL SELECT'ling',N'炩' UNION ALL SELECT'liu',N'咯'--瓼甅囖咯 UNION ALL SELECT'long',N'贚' UNION ALL SELECT'lou',N'鏤' UNION ALL SELECT'lu',N'氇' UNION ALL SELECT'lv',N'鑢' UNION ALL SELECT'luan',N'亂' UNION ALL SELECT'lue',N'擽' UNION ALL SELECT'lun',N'論' UNION ALL SELECT'luo',N'鱳' UNION ALL SELECT'ma',N'嘛' UNION ALL SELECT'mai',N'霢' UNION ALL SELECT'man',N'蘰' UNION ALL SELECT'mang',N'蠎' UNION ALL SELECT'mao',N'唜' UNION ALL SELECT'me',N'癦'--癦呅 UNION ALL SELECT'mei',N'嚜' UNION ALL SELECT'men',N'們' UNION ALL SELECT'meng',N'霥'--霿踎 UNION ALL SELECT'mi',N'羃' UNION ALL SELECT'mian',N'麵' UNION ALL SELECT'miao',N'廟' UNION ALL SELECT'mie',N'鱴'--鱴瓱 UNION ALL SELECT'min',N'鰵' UNION ALL SELECT'ming',N'詺' UNION ALL SELECT'miu',N'謬' UNION ALL SELECT'mo',N'耱'--耱乮 UNION ALL SELECT'mou',N'麰'--麰蟱 UNION ALL SELECT'mu',N'旀' UNION ALL SELECT'na',N'魶' UNION ALL SELECT'nai',N'錼' UNION ALL SELECT'nan',N'婻' UNION ALL SELECT'nang',N'齉' UNION ALL SELECT'nao',N'臑' UNION ALL SELECT'ne',N'呢' UNION ALL SELECT'nei',N'焾'--嫩焾 UNION ALL SELECT'nen',N'嫩' UNION ALL SELECT'neng',N'能'--莻嗯鈪銰啱 UNION ALL SELECT'ni',N'嬺' UNION ALL SELECT'nian',N'艌' UNION ALL SELECT'niang',N'釀' UNION ALL SELECT'niao',N'脲' UNION ALL SELECT'nie',N'钀' UNION ALL SELECT'nin',N'拰' UNION ALL SELECT'ning',N'濘' UNION ALL SELECT'niu',N'靵' UNION ALL SELECT'nong',N'齈' UNION ALL SELECT'nou',N'譳' UNION ALL SELECT'nu',N'搙' UNION ALL SELECT'nv',N'衄' UNION ALL SELECT'nue',N'瘧' UNION ALL SELECT'nuan',N'燶'--硸黁燶郍 UNION ALL SELECT'nuo',N'桛' UNION ALL SELECT'o',N'鞰'--毮夞乯鞰 UNION ALL SELECT'ou',N'漚' UNION ALL SELECT'pa',N'袙' UNION ALL SELECT'pai',N'磗'--鎃磗 UNION ALL SELECT'pan',N'鑻' UNION ALL SELECT'pang',N'胖' UNION ALL SELECT'pao',N'礮' UNION ALL SELECT'pei',N'轡' UNION ALL SELECT'pen',N'喯' UNION ALL SELECT'peng',N'喸'--浌巼闏乶喸 UNION ALL SELECT'pi',N'鸊' UNION ALL SELECT'pian',N'騙' UNION ALL SELECT'piao',N'慓' UNION ALL SELECT'pie',N'嫳' UNION ALL SELECT'pin',N'聘' UNION ALL SELECT'ping',N'蘋' UNION ALL SELECT'po',N'魄' UNION ALL SELECT'pou',N'哛'--兺哛 UNION ALL SELECT'pu',N'曝' UNION ALL SELECT'qi',N'蟿' UNION ALL SELECT'qia',N'髂' UNION ALL SELECT'qian',N'縴' UNION ALL SELECT'qiang',N'瓩'--羻兛瓩 UNION ALL SELECT'qiao',N'躈' UNION ALL SELECT'qie',N'籡' UNION ALL SELECT'qin',N'藽' UNION ALL SELECT'qing',N'櫦' UNION ALL SELECT'qiong',N'瓗' UNION ALL SELECT'qiu',N'糗' UNION ALL SELECT'qu',N'覻' UNION ALL SELECT'quan',N'勸' UNION ALL SELECT'que',N'礭' UNION ALL SELECT'qun',N'囕' UNION ALL SELECT'ran',N'橪' UNION ALL SELECT'rang',N'讓' UNION ALL SELECT'rao',N'繞' UNION ALL SELECT're',N'熱' UNION ALL SELECT'ren',N'餁' UNION ALL SELECT'reng',N'陾' UNION ALL SELECT'ri',N'馹' UNION ALL SELECT'rong',N'穃' UNION ALL SELECT'rou',N'嶿' UNION ALL SELECT'ru',N'擩' UNION ALL SELECT'ruan',N'礝' UNION ALL SELECT'rui',N'壡' UNION ALL SELECT'run',N'橍'--橍挼 UNION ALL SELECT'ruo',N'鶸' UNION ALL SELECT'sa',N'栍'--櫒栍 UNION ALL SELECT'sai',N'虄'--簺虄 UNION ALL SELECT'san',N'閐' UNION ALL SELECT'sang',N'喪' UNION ALL SELECT'sao',N'髞' UNION ALL SELECT'se',N'飋'--裇聓 UNION ALL SELECT'sen',N'篸' UNION ALL SELECT'seng',N'縇'--閪縇 UNION ALL SELECT'sha',N'霎' UNION ALL SELECT'shai',N'曬' UNION ALL SELECT'shan',N'鱔' UNION ALL SELECT'shang',N'緔' UNION ALL SELECT'shao',N'潲' UNION ALL SELECT'she',N'欇' UNION ALL SELECT'shen',N'瘮' UNION ALL SELECT'sheng',N'賸' UNION ALL SELECT'shi',N'瓧'--鰘齛兙瓧 UNION ALL SELECT'shou',N'鏉' UNION ALL SELECT'shu',N'虪' UNION ALL SELECT'shua',N'誜' UNION ALL SELECT'shuai',N'卛' UNION ALL SELECT'shuan',N'腨' UNION ALL SELECT'shuang',N'灀' UNION ALL SELECT'shui',N'睡' UNION ALL SELECT'shun',N'鬊' UNION ALL SELECT'shuo',N'鑠' UNION ALL SELECT'si',N'乺'--瀃螦乺 UNION ALL SELECT'song',N'鎹' UNION ALL SELECT'sou',N'瘶' UNION ALL SELECT'su',N'鷫' UNION ALL SELECT'suan',N'算' UNION ALL SELECT'sui',N'鐩' UNION ALL SELECT'sun',N'潠' UNION ALL SELECT'suo',N'蜶' UNION ALL SELECT'ta',N'襨'--躢襨 UNION ALL SELECT'tai',N'燤' UNION ALL SELECT'tan',N'賧' UNION ALL SELECT'tang',N'燙' UNION ALL SELECT'tao',N'畓'--討畓 UNION ALL SELECT'te',N'蟘' UNION ALL SELECT'teng',N'朰'--霯唞朰 UNION ALL SELECT'ti',N'趯' UNION ALL SELECT'tian',N'舚' UNION ALL SELECT'tiao',N'糶' UNION ALL SELECT'tie',N'餮' UNION ALL SELECT'ting',N'乭'--濎乭 UNION ALL SELECT'tong',N'憅' UNION ALL SELECT'tou',N'透' UNION ALL SELECT'tu',N'鵵' UNION ALL SELECT'tuan',N'褖' UNION ALL SELECT'tui',N'駾' UNION ALL SELECT'tun',N'坉' UNION ALL SELECT'tuo',N'籜' UNION ALL SELECT'wa',N'韤' UNION ALL SELECT'wai',N'顡' UNION ALL SELECT'wan',N'贎' UNION ALL SELECT'wang',N'朢' UNION ALL SELECT'wei',N'躛' UNION ALL SELECT'wen',N'璺' UNION ALL SELECT'weng',N'齆' UNION ALL SELECT'wo',N'齷' UNION ALL SELECT'wu',N'鶩' UNION ALL SELECT'xi',N'衋' UNION ALL SELECT'xia',N'鏬' UNION ALL SELECT'xian',N'鼸' UNION ALL SELECT'xiang',N'鱌' UNION ALL SELECT'xiao',N'斆' UNION ALL SELECT'xie',N'躞' UNION ALL SELECT'xin',N'釁' UNION ALL SELECT'xing',N'臖' UNION ALL SELECT'xiong',N'敻' UNION ALL SELECT'xiu',N'齅' UNION ALL SELECT'xu',N'蓿' UNION ALL SELECT'xuan',N'贙' UNION ALL SELECT'xue',N'瀥' UNION ALL SELECT'xun',N'鑂' UNION ALL SELECT'ya',N'齾' UNION ALL SELECT'yan',N'灩' UNION ALL SELECT'yang',N'樣' UNION ALL SELECT'yao',N'鑰' UNION ALL SELECT'ye',N'岃'--鸈膶岃 UNION ALL SELECT'yi',N'齸' UNION ALL SELECT'yin',N'檼' UNION ALL SELECT'ying',N'譍' UNION ALL SELECT'yo',N'喲' UNION ALL SELECT'yong',N'醟' UNION ALL SELECT'you',N'鼬' UNION ALL SELECT'yu',N'爩' UNION ALL SELECT'yuan',N'願' UNION ALL SELECT'yue',N'鸙' UNION ALL SELECT'yun',N'韻' UNION ALL SELECT'za',N'雥' UNION ALL SELECT'zai',N'縡' UNION ALL SELECT'zan',N'饡' UNION ALL SELECT'zang',N'臟' UNION ALL SELECT'zao',N'竈' UNION ALL SELECT'ze',N'稄' UNION ALL SELECT'zei',N'鱡' UNION ALL SELECT'zen',N'囎' UNION ALL SELECT'zeng',N'贈' UNION ALL SELECT'zha',N'醡' UNION ALL SELECT'zhai',N'瘵' UNION ALL SELECT'zhan',N'驏' UNION ALL SELECT'zhang',N'瞕' UNION ALL SELECT'zhao',N'羄' UNION ALL SELECT'zhe',N'鷓' UNION ALL SELECT'zhen',N'黮' UNION ALL SELECT'zheng',N'證' UNION ALL SELECT'zhi',N'豒' UNION ALL SELECT'zhong',N'諥' UNION ALL SELECT'zhou',N'驟' UNION ALL SELECT'zhu',N'鑄' UNION ALL SELECT'zhua',N'爪' UNION ALL SELECT'zhuai',N'跩' UNION ALL SELECT'zhuan',N'籑' UNION ALL SELECT'zhuang',N'戅' UNION ALL SELECT'zhui',N'鑆' UNION ALL SELECT'zhun',N'稕' UNION ALL SELECT'zhuo',N'籱' UNION ALL SELECT'zi',N'漬'--漬唨 UNION ALL SELECT'zong',N'縱' UNION ALL SELECT'zou',N'媰' UNION ALL SELECT'zu',N'謯' UNION ALL SELECT'zuan',N'攥' UNION ALL SELECT'zui',N'欈' UNION ALL SELECT'zun',N'銌' UNION ALL SELECT'zuo',N'咗') t WHERE word >= @word COLLATE Chinese_PRC_CS_AS_KS_WS ORDER BY word COLLATE Chinese_PRC_CS_AS_KS_WS ASC)ELSE @word END ) SET @i = @i + 1 END RETURN @pinyin END GO
/****** Object: UserDefinedFunction [dbo].[fun_getAbbFirstPY] Script Date: 01/08/2017 14:28:53 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create FUNCTION [dbo].[fun_getAbbFirstPY](@str nvarchar(4000) ) RETURNS nvarchar(4000) AS BEGIN DECLARE @word nchar(1),@PY nvarchar(4000) set @PY='' WHILE len(@str)>0 BEGIN set @word=left(@str,1) --如果非汉字字符，返回原字符 set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 then ( select top 1 PY from ( select 'A' as PY,N'驁' as word union all select 'B',N'簿' union all select 'C',N'錯' union all select 'D',N'鵽' union all select 'E',N'樲' union all select 'F',N'鰒' union all select 'G',N'腂' union all select 'H',N'夻' union all select 'J',N'攈' union all select 'K',N'穒' union all select 'L',N'鱳' union all select 'M',N'旀' union all select 'N',N'桛' union all select 'O',N'漚' union all select 'P',N'曝' union all select 'Q',N'囕' union all select 'R',N'鶸' union all select 'S',N'蜶' union all select 'T',N'籜' union all select 'W',N'鶩' union all select 'X',N'鑂' union all select 'Y',N'韻' union all select 'Z',N'咗' ) T where word>=@word collate Chinese_PRC_CS_AS_KS_WS order by PY ASC ) else @word end) SET @str=right(@str,len(@str)-1) END RETURN @PY END GO
-----------------------------------------------
--初始化系统参数表
--------------
INSERT INTO [PaaSCustDB].[dbo].[sysComOption] ([Code] ,[ParentCode],[Version],[Status],[OptionType] ,[OptionName],[OptionCNName],[OptionDisplayName],[OptionAbbPYName],[OptionFullPYName], [OptionValueType],[OptionDisplayValue] ,[OptionValueNvarchar], [Comments],[Create_date],[Lastmodify_date]) SELECT [code] ,[parentcode] ,1 ,1 ,'sysType001Region', dbo.fn_GetFullPinyin([Regionname],0),[Regionname],[Regionname],dbo.fun_getAbbFirstPY([Regionname]),dbo.fn_GetFullPinyin([Regionname],0) ,5,[Regionname],[Regionname] ,[parentRegionname]+':'+[GPS],GETDATE(),GETDATE() FROM [PaaSCustDB].[dbo].[regionTemp]
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='tab_user' --如果只查询指定表,加上此条件 order by a.id,a.colorder
select A.[name],B.value,A.type_desc,A.create_date,A.modify_date,A.[object_id] from sys.all_objects A inner join sys.extended_properties B on A.object_id = B.major_id and B.minor_id=0 where type_desc='USER_TABLE'