Determining the Column Widths of a field in a SQL Server 2008 Query

How do I find the column widths from the fields in the RAMS section of the Select statement......The query.
I am trying to identify the widths of the field to match them up against a TEMP table i have. Can someone help me with this. Thank you.

declare     @Cus_no_Start           varchar(12), 
            @Cus_no_End             varchar(12),
--          @Rams_Cus_no_Start      varchar(12),
--          @Rams_Cus_no_End  varchar(12),
            @DB_Include             int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS 

set @Cus_no_Start = '1'
set @Cus_no_End = '1999'
set @DB_Include = 2
declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
   @CurrWeekEndDate     = CurrWeekEndDate, 
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrFromDateYTD           = CurrFromDateYTD,
   @CurrFromDate        = CurrFromDate,
   @CurrToDate                = CurrToDate, 
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrWeekBeginDate   = CurrWeekBeginDate,
   @PriorWeekBeginDate = PriorWeekBeginDate,
   @PriorWeekEndDate    = PriorWeekEndDate,
   @PriorFromDateYTD    = PriorFromDateYTD,
   @PriorToDateYTD            = PriorToDateYTD,
   @PriorFromDate             = PriorFromDate,
   @PriorToDate         = PriorToDate
from dbo.Rpt_ReportingDates  WITH (NOLOCK) 

declare @CurrentWeek decimal(8,0), 
            @NumberOfWeeks int, 
            @CurrToDate_Period tinyint, 
            @CurrToDateYTD_FiscalYear smallint
declare @CurrToDate_pSDt datetime, 
            @CurrToDate_pSDt7 datetime

select 
      @CurrentWeek                        = CurrentWeek,
      @NumberOfWeeks                      = NumberOfWeeks,
      @CurrToDate_Period                  = CurrToDate_Period,
      @CurrToDateYTD_FiscalYear     = CurrToDateYTD_FiscalYear,
      @CurrToDate_pSDt              = CurrToDate_PeriodStartDate,
      @CurrToDate_pSDt7             = CurrToDate_PeriodStartDate+(7*vwFiscalDates.NumberOfWeeks-1)

from dbo.vwFiscalDates  --  WITH (NOLOCK)



      SELECT DISTINCT
                
          'Rams',
            CM.CM_SELLZONE,
            @CurrWeekEndDate as CurrWeekEndDate, 
            @CurrToDate as CurrToDate, 
            @CurrToDateYTD as CurrToDateYTD, 
            @CurrentWeek as CurrentWeek,
            @NumberOfWeeks as NumberOfWeeks,
            @CurrToDate_Period as CurrToDate_Period,
            @CurrToDateYTD_FiscalYear as CurrToDateYTD_FiscalYear,
            CUST_ID.ID_DESC as Terr_name,
            CUST_ID.ID_Title as ID_Title, 
            Rpt_TerritoryAdopted.Report_Sort, 
            SUBSTRING(CUST_ID.ID_DESC, 3, 2) AS Terr,
            OH.OH_CUSTOMER_NUMBER,
            CM.CM_FULLNAME,
            Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
            Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
            Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
            FLOOR((CONVERT(INT, OH.OH_DELIVERY_DATE - @CurrToDate_pSDt,112))/7+1) AS WeekNumber,
            CONVERT(char(8), @CurrToDate_pSDt+(7*FLOOR(CONVERT(INT, OH.OH_DELIVERY_DATE - @CurrToDate_pSDt)/7+1)-1),112) AS Week1_EndDate,
            OD_TICKET_NUMBER,
            OD.OD_PRODUCT as Item_no,
            OD.OD_RETURN_REASON_CODE,
            OD.OD_FINAL_PRICE,
            OD.OD_TOTAL_DELIV_QUANTITY, 
            Week_Sls_Amt = (CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                      AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
                                                      AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')) -- Sale or BuyBack
                                             THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                             ELSE 0 END),
            Week_Cost_Amt = (CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                      AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001'))                                                                                                                                                                  
                                             THEN     COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                             ELSE 0 END),
           OH.OH_delivery_date,
           OD_TOTAL_DELIV_QUANTITY,
           CONVERT(char(8), OH.OH_DELIVERY_DATE ,112),
         PD.PRD_A_GLTYPE,   --coalesce (right(PD.PRD_A_GLTYPE,2), '00'),  -- AS ProdCatNo,
         PD.PRD_DESC1,
         @CurrWeekBeginDate AS CurrWeekBeginDate,
            @PriorWeekBeginDate AS PriorWeekBeginDate,
            @PriorWeekEndDate AS PriorWeekEndDate,
            @CurrFromDate as CurrFromDate,
            @PriorFromDate AS PriorFromDate,
            @PriorToDate AS PriorToDate,
            @CurrFromDateYTD AS CurrFromDateYTD,
            @PriorFromDateYTD AS PriorFromDateYTD,
            @PriorToDateYTD AS PriorToDateYTD,
            qty_return_to_stk = 0
            
         
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH  
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  
                        ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                        AND CM.CM_ACCT_GLPOSTS = '000004'
                        --AND left(CM.CM_SELLZONE,1) = '4'
                        --AND CM.CM_CUST_SUB_TYPE <> 3
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD   
                        ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER    
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD 
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'
        --INNER JOIN dbo.Rpt_Territory as Rpt_TerritoryAdopted on Rpt_TerritoryAdopted.Terr_id  = SUBSTRING(CUST_ID.ID_DESC, 3, 2)
            LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted 
                                    ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
                                    AND Rpt_TerritoryAdopted.End_Item_no
	--Into Mytest

Open in new window

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If by 'widths' you mean the character lengths, then either looking at the table in Design view, or one of many T-SQL ways is
exec sp_help 'Your Table Name'

Open in new window

So ... what exactly do you mean by 'widths'?
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
How may character widths of the field. Is it 24 characters wide is it 60 characters wide.....? Maybe that is the wrong terminology but it is all I could think of.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I am trying to identify the widths of the field to match them up against a TEMP table i have.
Another way to pull this off, assuming that you're declaring the temp table in your SP, is to just script out a CREATE TABLE on the table we're talking about, which will generate a CREATE TABLE statement with all the widths, then just copy-paste into your SP where you're creating the temp table.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Brian CroweDatabase AdministratorCommented:
maybe this will help if you're just looking for column information on your table...just replace the 'myTableName' reference

SELECT o.name, c.name, t.name,
      CASE c.max_length WHEN -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END AS max_length
FROM sys.objects AS o
INNER JOIN sys.columns AS c
      ON o.object_id = c.object_id
INNER JOIN sys.types AS t
      ON c.user_type_id = t.user_type_id
WHERE o.name = 'myTableName'
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Jim Horn
Do I just put in the CREATE TABLE some where in this this Query and I can use the code you initially provided below......

exec sp_help 'Your Table Name' .........Sorry I get a little lost in here.

I am getting results from the Query I thought there would be a simple way to select a column from query and see the column lengths or what I would call a field width. My world is a Crystal Reports world.....ask mlmcc.


declare     @Cus_no_Start           varchar(12), 
            @Cus_no_End             varchar(12),
--          @Rams_Cus_no_Start      varchar(12),
--          @Rams_Cus_no_End  varchar(12),
            @DB_Include             int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS 

set @Cus_no_Start = '1'
set @Cus_no_End = '1999'
set @DB_Include = 2
declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
   @CurrWeekEndDate     = CurrWeekEndDate, 
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrFromDateYTD           = CurrFromDateYTD,
   @CurrFromDate        = CurrFromDate,
   @CurrToDate                = CurrToDate, 
   @CurrToDateYTD             = CurrToDateYTD,
   @CurrWeekBeginDate   = CurrWeekBeginDate,
   @PriorWeekBeginDate = PriorWeekBeginDate,
   @PriorWeekEndDate    = PriorWeekEndDate,
   @PriorFromDateYTD    = PriorFromDateYTD,
   @PriorToDateYTD            = PriorToDateYTD,
   @PriorFromDate             = PriorFromDate,
   @PriorToDate         = PriorToDate
from dbo.Rpt_ReportingDates  WITH (NOLOCK) 

declare @CurrentWeek decimal(8,0), 
            @NumberOfWeeks int, 
            @CurrToDate_Period tinyint, 
            @CurrToDateYTD_FiscalYear smallint
declare @CurrToDate_pSDt datetime, 
            @CurrToDate_pSDt7 datetime

select 
      @CurrentWeek                        = CurrentWeek,
      @NumberOfWeeks                      = NumberOfWeeks,
      @CurrToDate_Period                  = CurrToDate_Period,
      @CurrToDateYTD_FiscalYear     = CurrToDateYTD_FiscalYear,
      @CurrToDate_pSDt              = CurrToDate_PeriodStartDate,
      @CurrToDate_pSDt7             = CurrToDate_PeriodStartDate+(7*vwFiscalDates.NumberOfWeeks-1)

from dbo.vwFiscalDates  --  WITH (NOLOCK)



      SELECT DISTINCT
              
          'Rams',
            CM.CM_SELLZONE,
            @CurrWeekEndDate as CurrWeekEndDate, 
            @CurrToDate as CurrToDate, 
            @CurrToDateYTD as CurrToDateYTD, 
            @CurrentWeek as CurrentWeek,
            @NumberOfWeeks as NumberOfWeeks,
            @CurrToDate_Period as CurrToDate_Period,
            @CurrToDateYTD_FiscalYear as CurrToDateYTD_FiscalYear,
            CUST_ID.ID_DESC as Terr_name,
            CUST_ID.ID_Title as ID_Title, 
            Rpt_TerritoryAdopted.Report_Sort, 
            SUBSTRING(CUST_ID.ID_DESC, 3, 2) AS Terr,
            OH.OH_CUSTOMER_NUMBER,
            CM.CM_FULLNAME,
            Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
            Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
            Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
            FLOOR((CONVERT(INT, OH.OH_DELIVERY_DATE - @CurrToDate_pSDt,112))/7+1) AS WeekNumber,
            CONVERT(char(8), @CurrToDate_pSDt+(7*FLOOR(CONVERT(INT, OH.OH_DELIVERY_DATE - @CurrToDate_pSDt)/7+1)-1),112) AS Week1_EndDate,
            OD_TICKET_NUMBER,
            OD.OD_PRODUCT as Item_no,
            OD.OD_RETURN_REASON_CODE,
            OD.OD_FINAL_PRICE,
            OD.OD_TOTAL_DELIV_QUANTITY, 
            Week_Sls_Amt = (CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                      AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
                                                      AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')) -- Sale or BuyBack
                                             THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                             ELSE 0 END),
            Week_Cost_Amt = (CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                      AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001'))                                                                                                                                                                  
                                             THEN     COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                             ELSE 0 END),
           OH.OH_delivery_date,
           OD_TOTAL_DELIV_QUANTITY,
           CONVERT(char(8), OH.OH_DELIVERY_DATE ,112),
         PD.PRD_A_GLTYPE,   --coalesce (right(PD.PRD_A_GLTYPE,2), '00'),  -- AS ProdCatNo,
         PD.PRD_DESC1,
         @CurrWeekBeginDate AS CurrWeekBeginDate,
            @PriorWeekBeginDate AS PriorWeekBeginDate,
            @PriorWeekEndDate AS PriorWeekEndDate,
            @CurrFromDate as CurrFromDate,
            @PriorFromDate AS PriorFromDate,
            @PriorToDate AS PriorToDate,
            @CurrFromDateYTD AS CurrFromDateYTD,
            @PriorFromDateYTD AS PriorFromDateYTD,
            @PriorToDateYTD AS PriorToDateYTD,
            qty_return_to_stk = 0
           
 Into Mytest
         
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH  
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  
                        ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                        AND CM.CM_ACCT_GLPOSTS = '000004'
                        --AND left(CM.CM_SELLZONE,1) = '4'
                        --AND CM.CM_CUST_SUB_TYPE <> 3
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD   
                        ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER    
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD 
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'
        --INNER JOIN dbo.Rpt_Territory as Rpt_TerritoryAdopted on Rpt_TerritoryAdopted.Terr_id  = SUBSTRING(CUST_ID.ID_DESC, 3, 2)
            LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted 
                                    ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
                                    AND Rpt_TerritoryAdopted.End_Item_no

Open in new window

QueryrResults.png
0
Brian CroweDatabase AdministratorCommented:
There are third party tools (I personally use RedGate's SQL Prompt) that will provide hover information and/or the ability to dive into a column/table for more information but it is not yet built into SSMS.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I tried all of these and they worked. Thank you. Surprised Microsoft never put in a feature that allows users to higlight a column after a Query has run that would allow users to see the field type and the width ..... basic stuff. That would be very helpful,....?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.