Post reply

Numeric column size checker

  • November 16, 2010 at 5:18 pm

    #229331

    Does anyone have a util or sproc that checks a table data types for fields? I have a field that has

    a precision of 11 numeric type and there was a max of 13 precision for that column. I want a script that will check all fields in the table to make sure they are within range.

    I see there is a Column size checker but that only works for char or varchar. I need one for numeric field types.

  • November 16, 2010 at 5:43 pm

    #1251655

    Hi,

    You can probably look at the system views (sys.columns). I have constructed a query using sys.columns. Hope this helps.

    select o.object_id,

    o.name,

    t.name,

    c.*

    from sys.columns c

    join

    sys.objects o

    on o.object_id = c.object_id

    join

    sys.types t

    on t.system_type_id = c.system_type_id

  • November 16, 2010 at 7:09 pm

    #1251666

    don't you just need to compare the convert of the column to itself to see if they are not equal to find items that are not in range?

    something like

    select

    TheValue,

    CONVERT(decimal(11),TheValue)

    FROM YourTable

    WHERE CONVERT(decimal(11),TheValue) <> TheValue

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 17, 2010 at 3:21 pm

    #1252186

    i have a 100 + million rec table I wanted to check the data types (specifically numeric) to make sure the data precision is not greater than what it is supposed to be. I have seen "out of range" errors when trying to import a table (sql 2000 ) to a sql 2008 db.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply