Issue with incorrect metadata ?

We're running SQL2k5 and I've got some stored procedures which all have the
last parameter as optional with a default value of zero i.e.
create procedure myproc
@.Parm1 int,
@.Parm2 int=0
when I query the system catalogs on this proc the rows returned do not
indicate the parameter as having a default value....I was planning to use
this information but cannot seem to figure out why this is wrong. The
sys.parameters column "has_default_value" is zero for every parameter in all
of our databases....in sys.syscolumns the cdefault is zero as well.
Is there somewhere else to find this data and be able to depend on it? I'm
really stuck here the whole team is waiting on me and I'm supposed to be
providing a home grown solution for automated building of .NET SqlCommand
objects based on this information.
select * from sys.parameters where object_id=2056602615
select * from sys.syscolumns where id=2056602615> when I query the system catalogs on this proc the rows returned do not
> indicate the parameter as having a default value....I was planning to use
> this information but cannot seem to figure out why this is wrong. The
> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
This is true, the information is not stored there (nor in
sys.all_parameters).
I ran a profiler trace and monitored expanding the parameters node under a
stored procedure in Management Studio (which shows "default" / "no default"
but not the actual value). Ignoring names/ids that are specific to my
environment, I saw this (my most relevant observation highlighted on line
13):
SELECT 'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(db_name(),'''')
+ ']' + '/StoredProcedure[@.Name=' + quotename(sp.name,'''')
+ ' and @.Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''')
+ ']' + '/Param[@.Name=' + quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar')
AND param.max_length <> -1 THEN param.max_length/2 ELSE
param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
null AS [DefaultValue], -- *********** NOTICE THIS ************
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param
ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = param.system_type_id
and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'fakeProcedure'
and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC
Nothing more promising showed up in the trace when scripting the object as
create to new window, or using the modify context menu option. Both seem to
just grab the code from sys.sql_modules and, in the case of modify, change
CREATE to ALTER -- without even bothering with the parameter list at all.
I looked at sp_sproc_columns, which I have spotted in profiler from time to
time, coming from an application that uses ODBC to call stored procedures.
But this procedure does not yield any information about default values. It
gets column_def from spt_sproc_columns_odbc_view (which I can't figure out
how to query directly) but it looks to be always null. I also tried to find
the source for spt_sproc_columns_odbc_view but it seems this may be locked
away in mssqlsystemresource db. The following yielded nothing:
use master;
go
select * from sys.all_objects where name = 'spt_sproc_columns_odbc_view';
select object_definition(object_id('spt_sproc_columns_odbc_view'));
select * from sys.sql_modules where object_id =object_id('spt_sproc_columns_odbc_view');
select * from sys.system_sql_modules where object_name(object_id) ='spt_sproc_columns_odbc_view';
Frankly, I think that SQL Server only stores this value in the text in
syscomments / sys.sql_modules. And when the node I mentioned above expands
it must parse the stored procedure text to see whether the parameter
declarations have = signs next to them or not. I couldn't find any other
way to get this information, and I remember it coming up during the beta and
I'm pretty sure it was closed as "won't fix." So unfortunately I think you
are stuck in the same boat; parsing
object_definition(object_id('procedure_name')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-parameters-for-a-function-or-stored-procedure.html
And this BOL article for SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms190340.aspx
Which says:
"SQL Server only maintains default values for CLR objects in this catalog
view; therefore, this column has a value of 0 for Transact-SQL objects. To
view the default value of a parameter in a Transact-SQL object, query the
definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
I have submitted a request for more clarification, and will follow up if I
get any useful information.
Cheers,
Aaron|||> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
I have submitted a suggestion to Microsoft regarding this issue through
"official" channels.
If you have a passport / Windows Live ID, you can see my feedback here, and
vote if you feel strongly enough about it:
http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143|||Books Online is pretty clear on this. Here's a quote from sys.parameters,
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm :
"SQL Server only maintains default values for CLR objects in this catalog view; therefore, this
column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a
Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
It has always been the case that we cannot get the default values of parameters in SQL Server. Seems
we now can get it for CLR procedures, but still not for TSQL objects. So same applies as for earlier
versions: parse the source code. You might want to post an enhancement request at:
http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We're running SQL2k5 and I've got some stored procedures which all have the last parameter as
> optional with a default value of zero i.e.
> create procedure myproc
> @.Parm1 int,
> @.Parm2 int=0
> when I query the system catalogs on this proc the rows returned do not indicate the parameter as
> having a default value....I was planning to use this information but cannot seem to figure out
> why this is wrong. The sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
> Is there somewhere else to find this data and be able to depend on it? I'm really stuck here the
> whole team is waiting on me and I'm supposed to be providing a home grown solution for automated
> building of .NET SqlCommand objects based on this information.
> select * from sys.parameters where object_id=2056602615
> select * from sys.syscolumns where id=2056602615
>|||Thanks for that reference...gives me alot to go on...
I wasn't trying to get the default value for a parameter...just the
knowledge that a parameter has a default value and can be considered
optional for input....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Op7OOSY$GHA.1220@.TK2MSFTNGP04.phx.gbl...
> Books Online is pretty clear on this. Here's a quote from sys.parameters,
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm
> :
> "SQL Server only maintains default values for CLR objects in this catalog
> view; therefore, this column has a value of 0 for Transact-SQL objects. To
> view the default value of a parameter in a Transact-SQL object, query the
> definition column of the sys.sql_modules catalog view, or use the
> OBJECT_DEFINITION system function."
> It has always been the case that we cannot get the default values of
> parameters in SQL Server. Seems we now can get it for CLR procedures, but
> still not for TSQL objects. So same applies as for earlier versions: parse
> the source code. You might want to post an enhancement request at:
> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
>> We're running SQL2k5 and I've got some stored procedures which all have
>> the last parameter as optional with a default value of zero i.e.
>> create procedure myproc
>> @.Parm1 int,
>> @.Parm2 int=0
>> when I query the system catalogs on this proc the rows returned do not
>> indicate the parameter as having a default value....I was planning to
>> use this information but cannot seem to figure out why this is wrong.
>> The sys.parameters column "has_default_value" is zero for every parameter
>> in all of our databases....in sys.syscolumns the cdefault is zero as
>> well.
>> Is there somewhere else to find this data and be able to depend on it?
>> I'm really stuck here the whole team is waiting on me and I'm supposed to
>> be providing a home grown solution for automated building of .NET
>> SqlCommand objects based on this information.
>> select * from sys.parameters where object_id=2056602615
>> select * from sys.syscolumns where id=2056602615
>>
>|||Voted!!!
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23AyWj3X$GHA.2328@.TK2MSFTNGP02.phx.gbl...
>> sys.parameters column "has_default_value" is zero for every parameter in
>> all of our databases....in sys.syscolumns the cdefault is zero as well.
> I have submitted a suggestion to Microsoft regarding this issue through
> "official" channels.
> If you have a passport / Windows Live ID, you can see my feedback here,
> and vote if you feel strongly enough about it:
> http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
>|||Here is a workaround for the time being (also posting it to the issue on
Connect).
I am also working on a version that retrieves the explicit default value,
but that is proving more cumbersome if the default value is a string and
contains a comma (but I am close).
ALTER PROCEDURE dbo.sys_GetParameters
@.object_name NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.object_id INT,
@.paramID INT,
@.paramName SYSNAME,
@.definition NVARCHAR(MAX),
@.t NVARCHAR(MAX),
@.loc1 INT,
@.loc2 INT,
@.loc3 INT,
@.loc4 INT,
@.has_default_value BIT;
SET @.object_id = OBJECT_ID(@.object_name);
IF (@.object_id IS NOT NULL)
BEGIN
SELECT @.definition = OBJECT_DEFINITION(@.object_id);
CREATE TABLE #params
(
parameter_id INT PRIMARY KEY,
has_default_value BIT NOT NULL DEFAULT (0)
);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT
parameter_id,
[name]
FROM
sys.parameters
WHERE
[object_id] = @.object_id;
OPEN c;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT
@.t = SUBSTRING
(
@.definition,
CHARINDEX(@.paramName, @.definition),
4000
),
@.has_default_value = 0;
SET @.loc1 = COALESCE(NULLIF(CHARINDEX('''', @.t), 0), 4000);
SET @.loc2 = COALESCE(NULLIF(CHARINDEX(',', @.t), 0), 4000);
SET @.loc3 = NULLIF(CHARINDEX('OUTPUT', @.t), 0);
SET @.loc4 = NULLIF(CHARINDEX('AS', @.t), 0);
SET @.loc1 = CASE WHEN @.loc2 < @.loc1 THEN @.loc2 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc3 < @.loc1 THEN @.loc3 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc4 < @.loc1 THEN @.loc4 ELSE @.loc1 END;
IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@.t, 1, @.loc1)))) > 0
SET @.has_default_value = 1;
INSERT #params
(
parameter_id,
has_default_value
)
SELECT
@.paramID,
@.has_default_value;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
END
SELECT
sp.[object_id],
[object_name] = @.object_name,
param_name = sp.[name],
sp.parameter_id,
type_name = UPPER(st.[name]),
sp.max_length,
sp.[precision],
sp.scale,
sp.is_output,
p.has_default_value
FROM
sys.parameters sp
INNER JOIN
#params p
ON
sp.parameter_id = p.parameter_id
INNER JOIN
sys.types st
ON
sp.user_type_id = st.user_type_id
WHERE
sp.[object_id] = @.object_id;
CLOSE c;
DEALLOCATE c;
DROP TABLE #params;
END
END
GO