[Sql Server][原创] - Sql Server 表或视图 - 字段信息维护过程和函数

过程和函数 依赖于：[Sql Server][原创] - 返回指定范围的Unicode字符 And 返回字符串的字节数（汉字二个，字母一个） 定义函数

-- 获取指定表名的字段类型信息并存儲到自建表:KH_AH_Using_TableViewColumnInfo

USE [EPICOR10]
GO
/****** Object:  StoredProcedure [dbo].[pro_KH_AH_Func_SetTableViewColumnDesc]    Script Date: 2017-12-22 08:52:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
    過程: dbo.KH_AH_Func_SetTableViewColumnDesc(@TableViewName)

    作者: 929412592@qq.com 輝創1989
        
    作用: 获取指定表名的字段类型信息并存儲到自建表:KH_AH_Using_TableViewColumnInfo

    测试:            

        exec dbo.KH_AH_Func_SetTableViewColumnDesc 'InvcHead',N'發票信息表',N'
        [Company]    :公司
        [OrderNum]    :銷售訂單
        [ClosedDate]:關閉日期
        ','',1

        select Unicode('9  99')


*/

ALTER PROC [dbo].[pro_KH_AH_Func_SetTableViewColumnDesc]
@TableViewName    nvarchar(500),
@TableDesc    nvarchar(max),
@ColumnDesc nvarchar(max),
@DelimStr    nvarchar(20),
@DoSave        bit,
@DoSearch    bit,
@CreateSql  nvarchar(max)
as    
    set @DelimStr = Case When Len(@DelimStr) = 0 then Char(10) else @DelimStr end

    create table #ColumnDesc(ParamID int identity(1,1),ParamValue nvarchar(max))
    insert into #ColumnDesc(ParamValue) 
    select Replace(Replace(ParamValue,Char(32),''),Char(9),'') from Erp.AH_CharDelimTable(@ColumnDesc,@DelimStr) where Replace(Replace(ParamValue,Char(10),''),Char(13),'') <> ''

    create table #ColumnTypeAndDescTable (
        [序号]                decimal(18, 0) identity(1,1) primary key,
        [类型]                nvarchar(500),
        [表/视图名]            nvarchar(500),
        [表/视图描述]        nvarchar(max),
        [字段名]            nvarchar(500),
        [字段描述]            nvarchar(max),
        [字段说明]            nvarchar(max),
        [类型编码]            int,
        [字段类型]            nvarchar(50),
        [字段类型描述]        nvarchar(50),
        [字段长度]            int,
        [精度]                int,
        [小数位]            int,
        [字段列表]            nvarchar(max),
        [第一序号]            int,
        [最长字段名]        int,
        [最长字段描述]        int,
        [最长字段類型描述]    int
    )

    insert into #ColumnTypeAndDescTable(
           [类型],
           [表/视图名],
           [字段名],
           [类型编码],
           [字段长度],
           [精度],
           [小数位]
    )
    select Case When A.xtype = 'U' Then 'Table' 
                When A.xtype = 'V' Then 'View' End    [类型],
           A.name                                    [表/视图名],
           '[' + B.name + ']'                        [字段名],
           B.xtype                                    [类型编码],
           B.length                                    [字段长度],
           B.prec                                    [精度],
           B.scale                                    [小数位]
      from [dbo].[sysobjects] A,[dbo].[syscolumns] B
     where A.id = B.id and A.xtype in ('U','V')and A.name = @TableViewName

    update #ColumnTypeAndDescTable set [字段类型] = B.Name from #ColumnTypeAndDescTable A,[dbo].[systypes] B where A.[类型编码] = B.xtype
        

    update #ColumnTypeAndDescTable set [字段类型描述] = [字段类型] where [字段类型] in ('bit','int','date','datetime','timestamp','uniqueidentifier')
    update #ColumnTypeAndDescTable set [字段类型描述] = Replace([字段类型] + '(' + cast( [精度] as nvarchar(5)) + ')','-1','max') where [字段类型] in ('nvarchar','nchar')
    update #ColumnTypeAndDescTable set [字段类型描述] = Replace([字段类型] + '(' + cast( [精度] as varchar(5)) + ')','-1','max') where [字段类型] in ('varchar','char')
    update #ColumnTypeAndDescTable set [字段类型描述] = [字段类型] + '(' + cast( [精度] as nvarchar(5)) + ',' + cast( [小数位] as nvarchar(5)) + ')' where [字段类型] = 'decimal'

    if(Len(@TableDesc) > 0)
    begin
        update #ColumnTypeAndDescTable set [表/视图描述] = @TableDesc
    end    
    
    declare @ColumnDescParams    nvarchar(max),
            @ColumnNameParam    nvarchar(max),
            @ColumnDescParam    nvarchar(max),
            @ColumnInfoParam    nvarchar(max),
            @DelimCol            nvarchar(max) = N':',
            @DelimInfo            nvarchar(max) = N'-'

    declare @ColumnIndex int = 1
    while exists(select * from #ColumnDesc A where A.ParamID = @ColumnIndex)
    begin    
        set @ColumnDescParams = (select ParamValue from #ColumnDesc where ParamID = @ColumnIndex)
            
        set @ColumnNameParam = Case When CharIndex(@DelimCol,@ColumnDescParams) > 0 Then Ice.entry(1,@ColumnDescParams,@DelimCol)                                             Else N'' End
        set @ColumnDescParam = Case When CharIndex(@DelimCol,@ColumnDescParams) > 0 Then Ice.entry(Ice.num_entries(@ColumnDescParams,@DelimCol),@ColumnDescParams,@DelimCol) Else N'' End
        set @ColumnInfoParam = Case When CharIndex(@DelimInfo,@ColumnDescParam) > 0 Then Ice.entry(Ice.num_entries(@ColumnDescParam,@DelimInfo),@ColumnDescParam,@DelimInfo) Else N'' End

        set @ColumnNameParam = RTrim(LTrim(Replace(Replace((@ColumnNameParam),Char(10),''),Char(13),'')))
        set @ColumnDescParam = RTrim(LTrim(Replace(Replace(Replace(Replace((@ColumnDescParam),Char(10),''),Char(13),''),@ColumnInfoParam,''),@DelimInfo,'')))
        set @ColumnInfoParam = RTrim(LTrim(Replace(Replace((@ColumnInfoParam),Char(10),''),Char(13),'')))

        update #ColumnTypeAndDescTable set [字段描述] = @ColumnDescParam,
                                           [字段说明] = @ColumnInfoParam  
         where [字段名] = @ColumnNameParam

        set @ColumnIndex = @ColumnIndex + 1
    end    

    update #ColumnTypeAndDescTable set [第一序号] = (select Min([序号]) from #ColumnTypeAndDescTable B where A.[表/视图名] = B.[表/视图名] and A.类型 = B.类型 Group By [类型])
      from #ColumnTypeAndDescTable A

    update #ColumnTypeAndDescTable set [最长字段名]          = (select Max(Len([字段名])) from #ColumnTypeAndDescTable B where A.[表/视图名] = B.[表/视图名] and A.[类型] = B.[类型] Group By [类型]),
                                       [最长字段描述]      = (select Max(Dbo.AH_GetStrLen(isNull([字段描述],'')))  from #ColumnTypeAndDescTable B where A.[表/视图名] = B.[表/视图名] and A.[类型] = B.[类型] Group By [类型]),
                                       [最长字段類型描述] = (select Max(Len(isNull([字段类型描述],''))) from #ColumnTypeAndDescTable B where A.[表/视图名] = B.[表/视图名] and A.[类型] = B.[类型] Group By [类型])
      from #ColumnTypeAndDescTable A

    update #ColumnTypeAndDescTable set [字段列表] = 
    Replace(Stuff((
    select '^' + [字段名] + Space([最长字段名] - Len([字段名])) + Char(9) + @DelimCol + ' ' + isNull([字段描述],'') + Space([最长字段描述] - Dbo.AH_GetStrLen(isNull([字段描述],''))) + Char(9) + 
           @DelimInfo + ' ' + isNull([字段说明],'') 
      from #ColumnTypeAndDescTable B where A.[表/视图名] = B.[表/视图名] and A.[类型] = B.[类型] for xml path('')),1,1,''),'^',Char(10))
      from #ColumnTypeAndDescTable A
     where [序号] = [第一序号]

    update #ColumnTypeAndDescTable set [字段列表] = 
    Replace(Stuff((select '^' + Char(9) + [字段名] + 
                                Space([最长字段名] - Len(isNull([字段名],''))) + Char(9) + isNull([字段类型描述],'') + ',' + 
                                Space([最长字段類型描述] - Len(isNull([字段类型描述],''))) + Char(9) + '-- ' + isNull([字段描述],'')  
      from #ColumnTypeAndDescTable B where A.[表/视图名] = B.[表/视图名] and A.[类型] = B.[类型] for xml path('')),1,1,''),'^',Char(10))
      from #ColumnTypeAndDescTable A
     where [序号] = [第一序号] + 1
    
    if(Len(@CreateSql) > 0)
    begin        
        update #ColumnTypeAndDescTable set [字段列表] = @CreateSql where [序号] = ([第一序号] + 2)
    end
    
    /*
        Dbo.AH_GetStrLen(isNull([字段描述],''))

        select Unicode(N'一'),Ascii(N'一')

        select Len(N'外部MES'),DataLength(N'外部MES')

        select * from KH_AH_Using_TableViewColumnInfo Order By [TableViewName],[Type]

        Drop Table KH_AH_Using_TableViewColumnInfo
    */
    if not exists(select * from sysobjects where name = 'KH_AH_Using_TableViewColumnInfo' and xtype = 'U')
    begin
        create table KH_AH_Using_TableViewColumnInfo(
            [Seq]                decimal(18, 0) identity(1,1) primary key,
            [Type]                nvarchar(500),
            [TableViewName]        nvarchar(500),
            [TableViewDesc]        nvarchar(max),
            [ColumnName]        nvarchar(500),
            [ColumnDesc]        nvarchar(max),
            [ColumnInfo]        nvarchar(max),
            [ColumnTypeCode]    int,
            [ColumnType]        nvarchar(50),
            [ColumnTypeInfo]    nvarchar(50),
            [ColumnSize]        int,
            [ColumnAccuracy]    int,
            [ColumnDecimal]        int,
            [ColumnMinSeq]        int,
            [ColumnMaxLen]        int,
            [ColumnInfoMaxLen]    int,
            [ColumnInfoList]    nvarchar(max)
        )
    end

    if(@DoSave = 1)
    begin
        delete from KH_AH_Using_TableViewColumnInfo where [TableViewName] = @TableViewName
        select * into #KH_AH_Using_TableViewColumnInfo from KH_AH_Using_TableViewColumnInfo
        Truncate table KH_AH_Using_TableViewColumnInfo
        insert into KH_AH_Using_TableViewColumnInfo(
               [Type],
               [TableViewName],
               [TableViewDesc],
               [ColumnName],
               [ColumnDesc],
               [ColumnInfo],
               [ColumnTypeCode],
               [ColumnType],
               [ColumnTypeInfo],
               [ColumnSize],
               [ColumnAccuracy],
               [ColumnDecimal],
               [ColumnMinSeq],
               [ColumnMaxLen],
               [ColumnInfoMaxLen],
               [ColumnInfoList]
        )
        select [Type],
               [TableViewName],
               [TableViewDesc],
               [ColumnName],
               [ColumnDesc],
               [ColumnInfo],
               [ColumnTypeCode],
               [ColumnType],
               [ColumnTypeInfo],
               [ColumnSize],
               [ColumnAccuracy],
               [ColumnDecimal],
               [ColumnMinSeq],
               [ColumnMaxLen],
               [ColumnInfoMaxLen],
               [ColumnInfoList] 
          from #KH_AH_Using_TableViewColumnInfo

        insert into KH_AH_Using_TableViewColumnInfo(
               [Type],
               [TableViewName],
               [TableViewDesc],
               [ColumnName],
               [ColumnDesc],
               [ColumnInfo],
               [ColumnTypeCode],
               [ColumnType],
               [ColumnTypeInfo],
               [ColumnSize],
               [ColumnAccuracy],
               [ColumnDecimal],
               [ColumnMinSeq],
               [ColumnMaxLen],
               [ColumnInfoMaxLen],
               [ColumnInfoList]
        )
        select [类型],
               [表/视图名],
               [表/视图描述],
               [字段名],
               [字段描述],
               [字段说明],
               [类型编码],
               [字段类型],
               [字段类型描述],
               [字段长度],
               [精度],
               [小数位],
               [第一序号],
               [最长字段名],
               [最长字段描述],
               [字段列表] 
          from #ColumnTypeAndDescTable
    end    

    if(@DoSearch = 1)
    begin
        select * from KH_AH_Using_TableViewColumnInfo where [TableViewName] = @TableViewName and [Type] = 'View'
        select * from KH_AH_Using_TableViewColumnInfo where [TableViewName] = @TableViewName and [Type] = 'Table'

        -- select * from #ColumnDesc
        -- select * from #ColumnTypeAndDescTable
    end

-- 获取指定表名的字段类型信息

USE [EPICOR10]
GO
/****** Object:  UserDefinedFunction [dbo].[AH_GetTableColumnType]    Script Date: 2017-12-22 14:40:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
    函数: Dbo.AH_GetTableColumnType(@TableName)

    作者: 929412592@qq.com 輝創1989
        
    作用: 获取指定表名的字段类型信息

    测试:
            select * from dbo.AH_GetTableColumnType('InvcHead')
            select * from dbo.AH_GetTableColumnType('InvcHead_UD')
*/

ALTER Function [dbo].[AH_GetTableColumnType]
(
    @TableName nvarchar(500)
)    
Returns @ColumnTypeTable Table (
    [序号]            decimal(18, 0) identity(1,1) primary key,
    [表名]            nvarchar(50),
    [表描述]        nvarchar(max),
    [字段名]        nvarchar(50),
    [字段描述]        nvarchar(max),
    [字段说明]        nvarchar(max),
    [类型编码]        int,
    [字段类型]        nvarchar(50),
    [字段类型描述]    nvarchar(50),
    [字段长度]        int,
    [精度]            int,
    [小数位]        int,
    [字段列表]        nvarchar(max),
    [最长字段名]    int,
    [最长字段描述]    int,
    [最长字段類型描述]    int
)
As
begin
    
    insert into @ColumnTypeTable(
           [表名],
           [字段名],
           [类型编码],
           [字段长度],
           [精度],
           [小数位]
    )
    select A.name                表名,
           '[' + B.name + ']'    字段名,
           B.xtype                类型编码,
           B.length                字段长度,
           B.prec                精度,
           B.scale                小数位
      from [dbo].[sysobjects] A,[dbo].[syscolumns] B
     where A.id = B.id and A.xtype = 'U'and A.name = @TableName

    update @ColumnTypeTable set [字段类型] = B.Name from @ColumnTypeTable A,[dbo].[systypes] B where A.[类型编码] = B.xtype        

    update @ColumnTypeTable set [字段类型描述] = [字段类型] where [字段类型] in ('bit','int','date','datetime','timestamp','uniqueidentifier')
    update @ColumnTypeTable set [字段类型描述] = Replace([字段类型] + '(' + cast( [精度] as nvarchar(5)) + ')','-1','max') where [字段类型] in ('nvarchar','nchar')
    update @ColumnTypeTable set [字段类型描述] = Replace([字段类型] + '(' + cast( [精度] as varchar(5)) + ')','-1','max') where [字段类型] in ('varchar','char')
    update @ColumnTypeTable set [字段类型描述] = [字段类型] + '(' + cast( [精度] as nvarchar(5)) + ',' + cast( [小数位] as nvarchar(5)) + ')' where [字段类型] = 'decimal'

    if exists(select * from [dbo].[sysobjects] where name = 'KH_AH_Using_TableViewColumnInfo' and xtype = 'U')
    begin
        if exists(select * from KH_AH_Using_TableViewColumnInfo where TableViewName = @TableName)
        begin
            update @ColumnTypeTable set [表描述]   = B.[TableViewDesc],
                                        [字段描述] = B.[ColumnDesc],
                                        [字段说明] = B.[ColumnInfo],
                                        [字段列表] = Case When A.[序号] = 3 Then B.[ColumnInfoList] Else Null End
              from @ColumnTypeTable A,KH_AH_Using_TableViewColumnInfo B where B.TableViewName = A.表名 and B.[ColumnName] = A.[字段名]
        end
    end        

    update @ColumnTypeTable set [最长字段名]        = (select Max(Len(isNull([字段名],''))) from @ColumnTypeTable)
    update @ColumnTypeTable set [最长字段描述]        = (select Max(Dbo.AH_GetStrLen(isNull([字段描述],''))) from @ColumnTypeTable)
    update @ColumnTypeTable set [最长字段類型描述]    = (select Max(Len(isNull([字段类型描述],''))) from @ColumnTypeTable)
    
    
    declare @DelimCol            nvarchar(max) = N':',
            @DelimInfo            nvarchar(max) = N'-'    

    update @ColumnTypeTable set [字段列表] = 
    Replace(Stuff((select '^' + [字段名] + 
                                Space([最长字段名] - Len(isNull([字段名],''))) + Char(9) + @DelimCol + ' ' + isNull([字段描述],'')  + 
                                Space([最长字段描述] - Dbo.AH_GetStrLen(isNull([字段描述],''))) + Char(9) + @DelimInfo + ' ' + isNull([字段说明],'')  
    from @ColumnTypeTable for xml path('')),1,1,''),'^',Char(10))
    where [序号] = 1

    update @ColumnTypeTable set [字段列表] = 
    Replace(Stuff((select '^' + Char(9) + [字段名] + 
                                Space([最长字段名] - Len(isNull([字段名],''))) + Char(9) + isNull([字段类型描述],'') + ',' + 
                                Space([最长字段類型描述] - Len(isNull([字段类型描述],''))) + Char(9) + '-- ' + isNull([字段描述],'')  
    from @ColumnTypeTable for xml path('')),1,1,''),'^',Char(10))
    where [序号] = 2    
    return
end

-- 获取指定视图名的字段类型信息

USE [EPICOR10]
GO
/****** Object:  UserDefinedFunction [dbo].[AH_GetViewColumnType]    Script Date: 2017-12-22 15:34:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
    函数: Dbo.AH_GetViewColumnType(@ViewName)

    作者: 929412592@qq.com 輝創1989
        
    作用: 获取指定视图名的字段类型信息

    测试:
            select * from dbo.AH_GetViewColumnType('InvcHead')

            select * from KH_AH_Using_TableViewColumnInfo
*/

ALTER Function [dbo].[AH_GetViewColumnType]
(
    @ViewName nvarchar(500)
)    
Returns @ColumnTypeTable Table (
    [序号]                decimal(18, 0) identity(1,1) primary key,
    [视图名]            nvarchar(50),
    [视图描述]            nvarchar(max),
    [字段名]            nvarchar(50),
    [字段描述]            nvarchar(max),
    [字段说明]            nvarchar(max),
    [类型编码]            int,
    [字段类型]            nvarchar(50),
    [字段类型描述]        nvarchar(50),
    [字段长度]            int,
    [精度]                int,
    [小数位]            int,
    [字段列表]            nvarchar(max),
    [最长字段名]        int,
    [最长字段描述]        int,
    [最长字段類型描述]    int
)
As
begin
    
    insert into @ColumnTypeTable(
           [视图名],
           [字段名],
           [类型编码],
           [字段长度],
           [精度],
           [小数位]
    )
    select A.name                视图名,
           '[' + B.name + ']'    字段名,
           B.xtype                类型编码,
           B.length                字段长度,
           B.prec                精度,
           B.scale                小数位
      from [dbo].[sysobjects] A,[dbo].[syscolumns] B
     where A.id = B.id and A.xtype = 'V'and A.name = @ViewName

    update @ColumnTypeTable set [字段类型] = B.Name from @ColumnTypeTable A,[dbo].[systypes] B where A.[类型编码] = B.xtype    

    update @ColumnTypeTable set [字段类型描述] = [字段类型] where [字段类型] in ('bit','int','date','datetime','timestamp','uniqueidentifier')
    update @ColumnTypeTable set [字段类型描述] = Replace([字段类型] + '(' + cast( [精度] as nvarchar(5)) + ')','-1','max') where [字段类型] in ('nvarchar','nchar')
    update @ColumnTypeTable set [字段类型描述] = Replace([字段类型] + '(' + cast( [精度] as varchar(5)) + ')','-1','max') where [字段类型] in ('varchar','char')
    update @ColumnTypeTable set [字段类型描述] = [字段类型] + '(' + cast( [精度] as nvarchar(5)) + ',' + cast( [小数位] as nvarchar(5)) + ')' where [字段类型] = 'decimal'

    if exists(select * from [dbo].[sysobjects] where name = 'KH_AH_Using_TableViewColumnInfo' and xtype = 'U')
    begin
        if exists(select * from KH_AH_Using_TableViewColumnInfo where TableViewName = @ViewName)
        begin
            update @ColumnTypeTable set [视图描述] = B.[TableViewDesc],
                                        [字段描述] = B.[ColumnDesc],
                                        [字段说明] = B.[ColumnInfo],
                                        [字段列表] = Case When A.[序号] = 3 Then B.[ColumnInfoList] Else Null End
              from @ColumnTypeTable A,KH_AH_Using_TableViewColumnInfo B where B.TableViewName = A.视图名 and B.[ColumnName] = A.[字段名]
        end
    end        

    update @ColumnTypeTable set [最长字段名]        = (select Max(Len(isNull([字段名],''))) from @ColumnTypeTable)
    update @ColumnTypeTable set [最长字段描述]        = (select Max(Dbo.AH_GetStrLen(isNull([字段描述],''))) from @ColumnTypeTable)
    update @ColumnTypeTable set [最长字段類型描述]    = (select Max(Len(isNull([字段类型描述],''))) from @ColumnTypeTable)

    declare @DelimCol            nvarchar(max) = N':',
            @DelimInfo            nvarchar(max) = N'-'

    update @ColumnTypeTable set [字段列表] = 
    Replace(Stuff((select '^' + [字段名] + 
                                Space([最长字段名] - Len(isNull([字段名],''))) + Char(9) + @DelimCol + ' ' + isNull([字段描述],'')  + 
                                Space([最长字段描述] - Dbo.AH_GetStrLen(isNull([字段描述],''))) + Char(9) + @DelimInfo + ' ' + isNull([字段说明],'')  
    from @ColumnTypeTable for xml path('')),1,1,''),'^',Char(10))
    where [序号] = 1

    update @ColumnTypeTable set [字段列表] = 
    Replace(Stuff((select '^' + Char(9) + [字段名] + 
                                Space([最长字段名] - Len(isNull([字段名],''))) + Char(9) + isNull([字段类型描述],'') + ',' + 
                                Space([最长字段類型描述] - Len(isNull([字段类型描述],''))) + Char(9) + '-- ' + isNull([字段描述],'')  
    from @ColumnTypeTable for xml path('')),1,1,''),'^',Char(10))
    where [序号] = 2
    return
end

-- 示范一：

declare @NSql                nvarchar(max),
            @NUpdateSql            nvarchar(max),
            @Sql                varchar(max),
            @UpdateSql            varchar(max),
            @Enter                nvarchar(50)    = Char(10) + Char(13),
            @NowDate            datetime        = getdate(),
            @Tick                nvarchar(10)    = '√',
            @NA                    nvarchar(10)    = '/',
            @DateFormat            int = 3,
            @NullDateHoldDay    int = 0,

            @TableDesc            nvarchar(max),
            @ColumnDesc            nvarchar(max),
            @DelimStr            nvarchar(20)    = '',
            @DoSave                bit                = 1,    
            @DoSearch            bit                = 0,
            @CreateSql            nvarchar(max)

    /*
        exec pro_KH_AH_HoldEpicorXFileRefView 'KH_AH_View_XFileRefLinkPart' 

        select * from dbo.AH_GetViewColumnType('KH_AH_View_XFileRefLinkPart')

        select Count(*) as PhotoCount from KH_AH_View_XFileRefLinkPart

        select * from KH_AH_View_XFileRefLinkPart where PartNum = 'HA17-SET-CI-01'

    */
    if(@ViewName = 'KH_AH_View_XFileRefLinkPart')
    begin    
        if exists(select * from sysobjects where name = 'KH_AH_View_XFileRefLinkPart' and xtype = 'V')
        begin
            drop view dbo.KH_AH_View_XFileRefLinkPart
        end

        set @CreateSql = N'
        create view dbo.KH_AH_View_XFileRefLinkPart
        as
        select Epicor10.Dbo.Part.Company                                                as [Company],
               Epicor10.Dbo.Part.PartNum                                                as [PartNum],
               Epicor10.Dbo.Part.PartDescription                                        as [PartDescription],
               Epicor10.Dbo.Part.ClassID                                                as [ClassID],
               Epicor10.Dbo.Part.ProdCode                                                as [ProdCode],
               Epicor10.Dbo.Part.KH_CalGroup_c                                            as [KHCalGroup],
               Epicor10.Dbo.Part.KH_PartOrigin_c                                        as [KHPartOrigin],
               Epicor10.Dbo.Part.KH_TigerGroup_c                                        as [KHTigerGroup],
               Epicor10.Ice.XFileAttch.AttachNum                                        as [AttachNum],
               Epicor10.Ice.XFileAttch.Key1                                                as [AttachKey1],
               Epicor10.Dbo.XFileRef.XFileRefNum                                        as [XFileRefNum],
               Epicor10.Dbo.XFileRef.XFileName                                            as [XFileName],
               Epicor10.Dbo.XFileRef.XFileDesc                                            as [XFileDesc],
               Epicor10.Dbo.XFileRef.DocTypeID                                            as [DocTypeID],
               Epicor10.Dbo.XFileRef.KH_PartNum_c                                        as [KHPartNum],
               Ice.Entry(1,Ice.Entry(2,Epicor10.Dbo.XFileRef.XFileDesc,''@''),''.'')    as [ImageNum]
          from Epicor10.Dbo.Part                                                                                                                                                             Left Outer Join
               Epicor10.Ice.XFileAttch On Epicor10.Dbo.Part.SysRowID = Epicor10.Ice.XFileAttch.ForeignSysRowID                                                                                Inner Join 
               Epicor10.Dbo.XFileRef   On Epicor10.Ice.XFileAttch.Company = Epicor10.Dbo.XFileRef.Company and Epicor10.Ice.XFileAttch.XFileRefNum = Epicor10.Dbo.XFileRef.XFileRefNum
         where Epicor10.Dbo.Part.PartNum = Epicor10.Dbo.XFileRef.KH_PartNum_c and Epicor10.Dbo.XFileRef.DocTypeID = ''IISProd''
        '
        exec(@CreateSql)

        set @TableDesc = N'產品主檔附件圖片視圖'
        set @ColumnDesc = N'
        [Company]            : 公司
        [PartNum]            : 產品編號
        [PartDescription]    : 產品描述
        [ClassID]            : 產品類別
        [ProdCode]           : 產品群組
        [KHCalGroup]         : 產品價值群組
        [KHPartOrigin]       : 產品歸屬
        [KHTigerGroup]       : Tiger分組
        [AttachNum]          : 附件索引號
        [AttachKey1]         : 附件索引關鍵值
        [XFileRefNum]        : 附件編號
        [XFileName]          : 附件路徑
        [XFileDesc]          : 附件名
        [DocTypeID]          : 附件類別
        [KHPartNum]          : 產品編號
        [ImageNum]           : 產品附件序號
        '
        exec dbo.pro_KH_AH_Func_SetTableViewColumnDesc @ViewName,@TableDesc,@ColumnDesc,@DelimStr,@DoSave,@DoSearch,@CreateSql
    end