June 30, 2014 at 11:36 pm
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 ???
July 1, 2014 at 5:14 am
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
July 1, 2014 at 6:31 am
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