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 for you help Razvan, all works perfect. Regards, Xavier [quoted text, click to view] "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 [quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" wrote:
> > -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%';
>
>
[quoted text, click to view] > -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'; [quoted text, click to view] > -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%';
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
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
[quoted text, click to view] > 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
Don't see what you're looking for? Try a search.
|
