Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Define Stored Procedure Columns

    The SQL below is the start of a massive Stored Procedure for Comparing two Datasets, which will be produced onto a report.

    I was wondering if I could call an SQLserver Procedure that would tell me the names of all the Columns that are produced by this SP, so I can print them out and more easily code the report?

    PHP Code:
    SELECT 
        stk
    .StockNumber,
        
    stk.DefaultImageName,
        
    tVTP.Make as PolMake,
        
    stkV.VehicleMake,
        
    tVTP.Model as PolModel,
        
    stkV.VehicleModel,
        
    tVTP.ModelNo as PolModelNo,
        
    stkV.VehicleModelNo,
        
    tVTP.EngineNumber as PolEngineNumber,
        
    Stk.EngineNumber,
        
    tVTP.comHeadLightNumber as PolHeadLightNumber,
        
    Stk.comHeadLightNumber,
        
    tVTP.comTailLightNumber as PolTailLightNumber,
        
    Stk.comTailLightNumber ,
        
    tVTP.comBumperLightNumber as PolBumperLightNumber,
        
    Stk.comBumperLightNumber,
        
    tVTP.comCornerLightNumber as PolCornerLightNumber,
        
    Stk.comCornerLightNumber,
        
    tVTP.Chassis as PolChassis,
        --
    Drive Train
        tVD
    .DriveTrainDescription as POlDriveTrainDescription,
        
    StktVD.DriveTrainDescription,
        --
    Body Type
        tVBT
    .BodyTypeDescription as PolBodyDescription ,
        
    StkVBT.BodyTypeDescription 
        

        FROM tblStock Stk
            
    --JOINS FOR THE Policy Definition
            INNER JOIN tblVehicles V 
                ON V
    .VehicleID Stk.VehicleID
            INNER JOIN tblVehicleType_Policy tVtP 
                ON tVTP
    .VehicleMaster V.VehicleMaster 
                
    AND tVTP.Make V.VehicleMake
                
    AND tVTP.Model V.VehicleModel
                
    AND tVTP.ModelNo V.VehicleModelNo
            INNER JOIN tblVehicleDriveTrain tVD ON
                tVD
    .vehicleDrivetrainID tVTP.DrivetrainID
            INNER JOIN tblvehicleBodyType tVBT ON
                tVBT
    .VehicleBodyTypeID tVTP.BodyTypeID


            
    --JOINS FOR the Stock Definition
            INNER JOIN tblVehicles STkV ON
                StkV
    .VehicleID Stk.VehicleID
            INNER JOIN tblvehicleBodyType StkVBT ON
                StkVBT
    .VehicleBodyTypeID Stk.BodyTypeID
            INNER JOIN tblVehicleDriveTrain StktVD ON
                StktVD
    .vehicleDrivetrainID stk.DrivetrainID

            
    --INNER JOIN tbl
    WHERE  Stk
    .StockNumber LIKE 'V%' 

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    see SET FMTONLY ON in books online.

  3. #3
    Join Date
    Aug 2007
    Posts
    17
    it is SET FMTONLY ON

  4. #4
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    What Does it Do?

  5. #5
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    That Doesn't really Help it just prints the Columns across the Top? how can this be a list?

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    Which database access library are you using? ADO, JDBC, ODBC all provide the metadata information along with the resultset from the database. You get this all in the same call. If you use FMTONLY ON, you get only the metadata.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't get it.
    You already have all the output columns listed in your code. It would take 60 seconds to create a list from this.
    If you have to do this for a bunch of sprocs, I suppose you could "SELECT INTO" a new table "WHERE 1=0", and then run a script like this: http://sqlblindman.googlepages.com/columnlister
    ...to enumerate all the columns.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •