Procedure Parameter length declaration less than Column length

  • is there any way or a tool to identify if in procedure the Parameter length was declarated less than table Column length ..

    I have a table

    CREATE TABLE TEST001 (KeyName Varchar(100) )

    a procedure

    CREATE PROCEDURE SpFindNames ( @KeyName VARCHAR(40) )

    AS

    BEGIN

    SELECT KeyName FROM TEST001

    WHERE KeyName = @KeyName

    END

    KeyName = @KeyName

    Here table Column with 100 char length "KeyName" was compared with SP parameter "@KeyName" with length 40 char ..

    IS there any way to find out all such usage on the ALL Procedures in the Database ???

  • If your sp parameter name is always same as the table column named, the query below can be helpful.

    select o.name,p.name as paraname,p.max_length as paralength, t.max_length, OBJECT_DEFINITION(o.object_id) Spdef

    from sys.objects o

    join sys.all_parameters p

    on o.object_id = p.object_id

    cross join (select o.name tblname,c.name colname ,c.max_length

    from sys.objects o

    join sys.columns c

    on o.object_id = c.object_id

    where o.type='U')t

    where type='p' and p.name like '@'+t.colname

    If the column names are not same then reading the Object definition can be helpful.

    Let me know if it is helpful.

    --Divya

  • Great thanks ..

    Its useful to start with .,rather looking on each proc ..

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

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