INFORMATION SCHEMA. PARAMETERS
CREATE VIEW INFORMATION_SCHEMA.PARAMETERS
AS
SELECT
db_name() AS SPECIFIC_CATALOG,
schema_name(o.schema_id) AS SPECIFIC_SCHEMA,
o.name AS SPECIFIC_NAME,
c.parameter_id AS ORDINAL_POSITION,
convert(nvarchar(10), CASE
WHEN c.parameter_id = 0 THEN ‘OUT’
WHEN c.is_output = 1 THEN ‘INOUT’
ELSE ‘IN’ END) AS PARAMETER_MODE,
convert(nvarchar(10), CASE WHEN
c.parameter_id = 0 THEN ‘YES’ ELSE ‘NO’ END) AS IS_RESULT,
convert(nvarchar(10), ‘NO’) AS AS_LOCATOR,
c.name AS PARAMETER_NAME,
type_name(c.system_type_id) AS DATA_TYPE,
ColumnProperty(c.object_id, c.name, ‘charmaxlen’) AS CHARACTER_MAXIMUM_LENGTH,
ColumnProperty(c.object_id, c.name, ‘octetmaxlen’) AS CHARACTER_OCTET_LENGTH,
convert(sysname, null) AS COLLATION_CATALOG,
convert(sysname, null) AS COLLATION_SCHEMA,
convert(sysname, CASE
WHEN c.system_type_id IN (35, 99, 167, 175, 231, 239) THEN — [n]char/[n]varchar/[n]text
ServerProperty(’collation’) END) AS COLLATION_NAME,
convert( sysname, null) AS CHARACTER_SET_CATALOG,
convert( sysname, null) AS CHARACTER_SET_SCHEMA,
convert( sysname, CASE
WHEN c.system_type_id IN (35, 167, 175) THEN ServerProperty(’sqlcharsetname’) — char/varchar/text
WHEN c.system_type_id IN (99, 231, 239) THEN N’UNICODE’ — nchar/nvarchar/ntext
END) AS CHARACTER_SET_NAME,
convert(tinyint, CASE — int/decimal/numeric/real/float/money
WHEN c.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN c.precision
END) AS NUMERIC_PRECISION,
convert(smallint, CASE — int/money/decimal/numeric
WHEN c.system_type_id IN (48, 52, 56, 60, 106, 108, 122, 127) THEN 10
WHEN c.system_type_id IN (59, 62) THEN 2 END) AS NUMERIC_PRECISION_RADIX, — real/float
convert(int, CASE — datetime/smalldatetime
WHEN c.system_type_id IN (58, 61) THEN NULL
ELSE odbcscale(c.system_type_id, c.scale) END) AS NUMERIC_SCALE,
convert(smallint, CASE — datetime/smalldatetime
WHEN c.system_type_id IN (58, 61) THEN 3 END) AS DATETIME_PRECISION,
convert(nvarchar(30), null) AS INTERVAL_TYPE,
convert(smallint, null) AS INTERVAL_PRECISION,
convert(sysname, CASE WHEN u.schema_id <> 4
THEN db_name() END) AS USER_DEFINED_TYPE_CATALOG,
convert(sysname, CASE WHEN u.schema_id <> 4
THEN schema_name(u.schema_id) END) AS USER_DEFINED_TYPE_SCHEMA,
convert(sysname, CASE WHEN u.schema_id <> 4
THEN u.name END) AS USER_DEFINED_TYPE_NAME,
convert(sysname, null) AS SCOPE_CATALOG,
convert(sysname, null) AS SCOPE_SCHEMA,
convert(sysname, null) AS SCOPE_NAME
FROM
sys.objects o JOIN sys.parameters c ON c.object_id = o.object_id
JOIN sys.types u ON u.user_type_id = c.user_type_id
WHERE
o.type IN (’P',’FN’,'TF’, ‘IF’, ‘IS’, ‘AF’,'PC’, ‘FS’, ‘FT’)
————