information_schema.columns

[wbodger]

So, I have a query that pulls data from tables given a specific form id. Form id is used to determine which db and then which table(s) to use for this report. Now, they want me to pull the metadata for each element on the form. So, coming into the query I do not know db, table or column, but I have the data I need to find them. How can I get to the SCHEMA_INFORMATION.COLUMNS view within the correct db if that db is pulled within the query itself? The existing query (yes, the data type column does not work):

SELECT 
	CDE.Base_ID AS CDEBaseID, 
    CASE  
        WHEN ( RTRIM(COALESCE(F2C.FRMDoc, '')) <> '') THEN REPLACE(RTRIM(F2C.FRMDoc),'<BR>','') 
        ELSE REPLACE(RTRIM(CDE.LongTerm),'<BR>','')
    END AS [Form Text], 
    CASE
		WHEN CDE.Base_ID = 422 THEN '18'
		ELSE '' 
	END AS [Permissible Values], 
	CF.Proto_ID,
    N.TBLNAME,
    RTRIM(CDE.VariableName) AS [Variable Name/Field Name], 
    REPLACE(RTRIM(COALESCE(CDE.Definition, '')),'<BR>','') AS [Definition], 
    CASE  
        WHEN ( RTRIM(COALESCE(F2C.FRMInstruction, '')) <> '') THEN REPLACE(RTRIM(F2C.FRMInstruction),'<BR>','') 
        ELSE REPLACE(RTRIM(COALESCE(CDE.Instructions, '')),'<BR>','') 
    END AS [Form Instructions], 
    RTRIM(CL.ObjectClass) AS [Object Class], 
    RTRIM(PROP.Property) AS [Property], 
    CDE.Version, PE.DE_ID, 
    RTRIM(R.Representation) AS [Representation],  
    dc.DBNAME.dbo.INFORMATION_SCHEMA.COLUMNS AS [Data Type], 
    CASE  
        WHEN ( RTRIM(COALESCE(PE.DE_Format, '')) <> '') THEN RTRIM(PE.DE_Format) 
        ELSE RTRIM(COALESCE(CDE.Format,'')) 
    END AS [Format], 
    CASE  
        WHEN PE.DE_MSize IS NOT NULL THEN CAST(PE.DE_MSize AS VARCHAR) 
        ELSE CAST(COALESCE(CDE.Max_Size, '') AS VARCHAR) 
    END AS [Max Size], 
    COALESCE(CAST(CDE.Min_Size AS VARCHAR), '') AS [Min Size], 
    CASE  
        WHEN PE.DmaxV IS NOT NULL THEN CAST(PE.DmaxV AS VARCHAR) 
        ELSE COALESCE(CAST(CDE.MAX_VALUE AS VARCHAR), '') 
    END AS [Max Value], 
    CASE  
        WHEN PE.DminV IS NOT NULL THEN CAST(PE.DminV AS VARCHAR) 
        ELSE COALESCE(CAST(CDE.MIN_Value AS VARCHAR), '') 
    END AS [Min Value], 
    COALESCE(CAST(CDE.Unit AS VARCHAR), '') AS [Unit], 
    REPLACE(COALESCE(CDE.Comments, ''),'<br>','') AS [Comments], 
	 CASE WHEN PE.DREQUIRE=1 THEN 'Yes' ELSE 'No' END AS [Required], 
	 CF.FormVersion, CF.Base_Id AS FormBaseID
FROM dbDEMapping.dbo.tblPElement PE 
    INNER JOIN dbDEMapping.dbo.tblCDEForm CF ON (PE.FORMID = CF.BASE_ID OR PE.FORMID = CF.FormID) 
    INNER JOIN dbDEMapping.dbo.tblForm2CDE F2C ON F2C.CDE_ID = PE.DE_ID AND F2C.FORM_ID = PE.FormID 
    INNER JOIN dbDEMapping.dbo.tblCDE CDE ON F2C.CDE_ID = CDE.BASE_ID OR F2C.CDE_ID = CDE.CDE_ID 
    INNER JOIN dbDEMapping.dbo.tblClass CL ON CDE.ObjectClass_ID = CL.ObjectClass_ID 
    INNER JOIN dbDEMapping.dbo.tblProperties PROP ON CDE.Property_ID = PROP.Property_ID 
    INNER JOIN dbDEMapping.dbo.tblDataType TYPE ON CDE.DataType_ID = TYPE.DataType_ID 
    INNER JOIN dbDEMapping.dbo.tblRepresentation R ON CDE.Representation_ID = R.Representation_ID 
    INNER JOIN dbDEMAPPING.dbo.tblTblName N ON PE.DTBL_ID = N.TBL_ID
    INNER JOIN (SELECT DISTINCT DBNAME, PROTO_ID FROM dbDEMapping.dbo.tblDataConnect where PROTO_ID= " & protocolid & ") AS dc ON CF.PROTO_ID = dc.PROTO_ID
WHERE PE.FORMID = & fid
AND CF.Version_Flag = 1

Having not done much with information_schema I find that I am a little bit of a loss as to how I would get the data type for each record in this query given the information I have to start with.. Hints tips or suggestions?

wb

 

[markros]

You will need to use dynamic sql here if DB is a variable.

PluralSight Learning Library

 

[wbodger]

OK. I was hoping that I was missing some clean way of wrapping this all up in one query in SQL. It is part of an ASP page, so I can use Dynamic SQL. Thx.

wb