2

I have a stored procedure called MyProc1. Most of the variable's datatype are matched with its source table's datatype, but a few are mismatched.

Is there any efficient way to find the mismatching variables?

Right now I manually take the description of the table and compare it with my variable datatype, but this is a time consuming task since I have to check around 500 plus stored procedures.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
StackUser
  • 5,370
  • 2
  • 24
  • 44
  • 1
    Do the parameters and columns have unique names that you could match or is everything just generic like id, code, name etc? – James Z Jul 13 '15 at 13:11
  • Variables declared as parameter is unique, but I want to check all other variables used inside stored procedure. – StackUser Jul 13 '15 at 13:28
  • If datatypes don't match and there is no explicit cast....there will be implicit casts. Search on _implicit casts_ and you'll find a lot of info like.. http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx – Nick.Mc Jul 13 '15 at 13:47

1 Answers1

2

Input parameters are easy enough to find, to borrow a query from another stack exchange answer:

select  
   'Parameter_name' = name,  
   'Type'   = type_name(user_type_id),  
   'Proc_Name' = object_name(object_id),
   'Length'   = max_length,  
   'Prec'   = case when type_name(system_type_id) = 'uniqueidentifier' 
              then precision  
              else OdbcPrec(system_type_id, max_length, precision) end,  
   'Scale'   = OdbcScale(system_type_id, scale),  
   'Param_order'  = parameter_id,  
   'Collation'   = convert(sysname, 
                   case when system_type_id in (35, 99, 167, 175, 231, 239)  
                   then ServerProperty('collation') end)  

  from sys.parameters

However parameters declared within the procedure are not accessible this way, to find those parameters you need to search within the procedures themselves:

  select OBJECT_NAME(id) as ProcName,  SUBSTRING(text, CHARINDEX('Declare @',text), 250) as DeclaredVarables
  from SYSCOMMENTS
  where CHARINDEX('Declare @',text) > 0
  order by OBJECT_NAME(id), CHARINDEX('Declare @',text)

Which will get you the procedure name and (hopefully) the relevant bits of the procedure. You may have to dial in the substring depending on coding standards, but that should give you a list of params declared within procedures.

Community
  • 1
  • 1
Randall
  • 1,441
  • 13
  • 19
  • Thanks, this was helpful. As we have SP's in varying schemes (ie not dbo), adding `'Schema' = OBJECT_SCHEMA_NAME(object_id),` before `Proc_name` was useful to see where they were. – Richard Moss Jul 11 '16 at 08:33