vis_Parameters

,

A view to examine the parameters in use by procedures, functions, etc. Hope you find it useful.

CREATE VIEW vis_Parameters
AS

/******
Object:
VIEW dbo.vis_Parameters
Description:

COMMAND = 
SELECT * FROM vis_Parameters
Author:

Create Date:
2008-01-15
Parameters:

Returns:
rowset
******/

SELECT
p.[Object_Id] AS ObjectId,
OBJECT_NAME(p.[object_id]) AS ObjectName,
CASE
WHEN OBJECTPROPERTY(p.[object_id],'IsProcedure')=1 THEN 'Procedure'
WHEN OBJECTPROPERTY(p.[object_id],'IsInlineFunction')=1 THEN 'Inline Function'
WHEN OBJECTPROPERTY(p.[object_id],'IsConstraint')=1 THEN 'Constraint'
WHEN OBJECTPROPERTY(p.[object_id],'IsScalarFunction')=1 THEN 'Scalar Function'
WHEN OBJECTPROPERTY(p.[object_id],'IsTable')=1 THEN 'Table'
WHEN OBJECTPROPERTY(p.[object_id],'IsTableFunction')=1 THEN 'Table-Valued Function'
WHEN OBJECTPROPERTY(p.[object_id],'IsTrigger')=1 THEN 'Trigger'
END AS ObjectType,
p.parameter_id,
CASE p.parameter_id WHEN 0 THEN 'RETURN value' ELSE p.[name] END AS ParameterName,
t.[name] AS DataType,
p.max_length,
p.[precision],
p.scale,
p.default_value

FROM sys.parameters p
INNER JOIN sys.types t ON p.system_type_id=t.user_type_id
WHERE
OBJECTPROPERTY(p.[object_id],'IsMSShipped') =0 -- not those delivered by MS

Rate

3.67 (3)

Share

Share

Rate

3.67 (3)