UFN_CODETABLE_GETDEPENDENTTABLECOLUMNS

Returns table spec columns whose code table name is equal to the given code table name.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@CODETABLENAME	nvarchar(100)	IN

Definition

Copy



            create function dbo.UFN_CODETABLE_GETDEPENDENTTABLECOLUMNS
            (
                @CODETABLENAME nvarchar(100)
            )
            returns table
            as
            return

                select
                    TABLECATALOG.ID as TABLECATALOGID,
                    TABLECATALOG.TABLENAME,
                    (select RECORDTYPE.ID from dbo.RECORDTYPE where RECORDTYPE.BASETABLENAME = TABLECATALOG.TABLENAME) as RECORDTYPEID,
                    TABLECATALOG.TABLESPECXML.query
                        (
                            'declare namespace bbspec="bb_appfx_table";
                            data(/bbspec:TableSpec/bbspec:Fields/bbspec:CodeTableField[@CodeTable=sql:variable("@CODETABLENAME")]/@Name)
                            '
                        ) as COLUMNNAMES
                from 
                    dbo.TABLECATALOG
                where
                    TABLECATALOG.TABLESPECXML.exist('
                        declare namespace bbspec="bb_appfx_table";        
                        /bbspec:TableSpec/bbspec:Fields/bbspec:CodeTableField[@CodeTable=sql:variable("@CODETABLENAME")]') = 1