字典关联

declare @dict_table nvarchar(50)
declare @dict_key nvarchar(50)
set @dict_table='AATest'
set @dict_key='ID'

SELECT  ROW_NUMBER() OVER(ORDER BY d.name),
a.name 表名,d.name 表名
,'INSERT INTO ['+@dict_table+'](['+@dict_key+'],[Name],[PID],[SettingItem],[Taxis],[Dstate])VALUES('+convert(varchar,ROW_NUMBER() OVER(ORDER BY d.name))+','''+substring(convert(varchar, ISNULL(g.[value], '')),0,charindex('(',replace(convert(varchar, ISNULL(g.[value], '')),' ','(')))+''',0,'''+convert(varchar, ISNULL(g.[value], ''))+''',0,0)'
      ,substring(convert(varchar, ISNULL(g.[value], '')),0,charindex('(',replace(convert(varchar, ISNULL(g.[value], '')),' ','(')))
 ,charindex('(',replace(convert(varchar, ISNULL(g.[value], '')),' ','(')),len(convert(varchar, ISNULL(g.[value], ''))),len(convert(varchar, ISNULL(g.[value], '')))-charindex('(',replace(convert(varchar, ISNULL(g.[value], '')),' ','('))
  , substring(convert(varchar, ISNULL(g.[value], '')),charindex('(',replace(convert(varchar, ISNULL(g.[value], '')),' ','(')),len(replace(convert(varchar, ISNULL(g.[value], '')),' ','('))-charindex('(',replace(convert(varchar, ISNULL(g.[value], '')),' ','('))+1)
      ,'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''+substring(convert(varchar, ISNULL(g.[value], '')),0,charindex('(',replace(convert(varchar, ISNULL(g.[value], '')),' ','(')))+convert(varchar,ROW_NUMBER() OVER(ORDER BY d.name))+''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''+d.name+''', @level2type=N''COLUMN'',@level2name=N'''+a.name+''''
      FROM dbo.syscolumns a LEFT OUTER JOIN
      dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
      dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND
      d.status >= 0 LEFT OUTER JOIN
      dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
      sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND
      g.name = 'MS_Description' LEFT OUTER JOIN
      sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND
      f.name = 'MS_Description'
      where (charindex(' ',convert(varchar, ISNULL(g.[value], '')))>0 or charindex('(',convert(varchar, ISNULL(g.[value], '')))>0)
      and b.name='int' --and charindex('(',replace(convert(varchar, ISNULL(g.[value], '')),' ','('))>0
      --and charindex('(',replace(convert(varchar, ISNULL(g.[value], '')),' ','('))+len(convert(varchar, ISNULL(g.[value], '')))-charindex('(',replace(convert(varchar, ISNULL(g.[value], '')),' ','('))<>len(convert(varchar, ISNULL(g.[value], '')))
--and a.name='AuditState'
ORDER BY d.name