Sql Server Programming :: Get information about parameter/function in SP (SQL2005)


	Get information about parameter/function in SP (SQL2005) Hi, Is it possible to get some additional information about Stored procedures in SQL2005 -i want to get to get the list of stored procedures which uses for example the parameter @vendor and type and size of this parameter (nchar,varchar,nvarcher..) in the SP, to detect if this parameter has differents definition? -also i want to get the list of StoredProcedures which uses for example the function MySqlFunction. Is there any possibilitie to do this over sql cmds Thanks, > -i want to get to get the list of stored procedures which uses for example > the parameter @vendor and type and size of this parameter > (nchar,varchar,nvarcher..) in the SP, to detect if this parameter has > differents definition? -- this should narrow the list down: SELECT * FROM sys.parameters WHERE name = 'vendor'; > -also i want to get the list of StoredProcedures which uses for example > the > function MySqlFunction. -- again, this isn't 100% accurate, but should be good in most cases: SELECT name FROM sys.procedures WHERE OBJECT_DEFINITION([object_id]) LIKE '%MySQLFunction%'; -----------------------------------------------Reply----------------------------------------------- Hello, Xavier To get the list of procedures that have a certain parameter, you can use: SELECT o.name as ObjectName, o.type as ObjectType, p.name as Parameter, t.name as DataType, p.max_length, p.precision, p.scale FROM sys.parameters p INNER JOIN sys.types t ON p.user_type_id=t.user_type_id INNER JOIN sys.objects o ON p.object_id=o.object_id WHERE p.name='@ParameterName' To get the list of objects that depend on a certain function, you can use: SELECT OBJECT_NAME(object_id) FROM sys.sql_dependencies WHERE referenced_major_id=OBJECT_ID('FunctionName') AND class IN (0,1) For more information, see "sys.sql_dependencies" (and the documentation for the other catalog views used in the above query) in Books Online. Razvan -----------------------------------------------Reply----------------------------------------------- Just wanted to add that sys.sql_dependencies is not 100% complete, because it does not contain the dependencies for objects that are created before the referenced objects, for objects used in dynamic sql, etc. Razvan -----------------------------------------------Reply----------------------------------------------- Thanks for you help Razvan, all works perfect. Regards, Xavier - Hide quoted text - "Razvan Socol" wrote: > Hello, Xavier > To get the list of procedures that have a certain parameter, you can > use: > SELECT o.name as ObjectName, o.type as ObjectType, p.name as > Parameter, > t.name as DataType, p.max_length, p.precision, p.scale > FROM sys.parameters p > INNER JOIN sys.types t ON p.user_type_id=t.user_type_id > INNER JOIN sys.objects o ON p.object_id=o.object_id > WHERE p.name='@ParameterName' > To get the list of objects that depend on a certain function, you can > use: > SELECT OBJECT_NAME(object_id) FROM sys.sql_dependencies > WHERE referenced_major_id=OBJECT_ID('FunctionName') > AND class IN (0,1) > For more information, see "sys.sql_dependencies" (and the > documentation for the other catalog views used in the above query) in > Books Online. > Razvan Thanks Bertrand, the second answer is perfect. By the first the list displays only the objectid and parameterid which is not so usefull. Regards, Xavier "Aaron Bertrand [SQL Server MVP]" wrote: - Hide quoted text - > > -i want to get to get the list of stored procedures which uses for example > > the parameter @vendor and type and size of this parameter > > (nchar,varchar,nvarcher..) in the SP, to detect if this parameter has > > differents definition? > -- this should narrow the list down: > SELECT * FROM sys.parameters WHERE name = 'vendor'; > > -also i want to get the list of StoredProcedures which uses for example > > the > > function MySqlFunction. > -- again, this isn't 100% accurate, but should be good in most cases: > SELECT name FROM sys.procedures WHERE OBJECT_DEFINITION([object_id]) LIKE > '%MySQLFunction%'; > the second answer is perfect. By the first the list displays only the > objectid and parameterid which is not so usefull. Okay, so you can do a little work on your own, no? I guess not... SELECT OBJECT_NAME([object_id]), ... You already have the parameter name (remember, you narrowed the list down using WHERE name = 'vendor') A