|
Home > Archive > ASE Database forum > October 2005 > sp_sproc_columns and sp_columns
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
sp_sproc_columns and sp_columns
|
|
| Frank Rizzo 2005-10-27, 8:21 am |
| I am trying to get a default value for a column in a table (or a stored
proc). To that effect, I am running sp_columns, which returns a
column_def column and it is always NULL.
I am also trying to get a default value for a stored proc parameter as
well (running sp_sproc_columns) and the column_def is also always NULL.
Am I missing something here? Adaptive Server Enterprise 12.5/SWR 9616
on win2k.
Regards
| |
| Bret Halford 2005-10-27, 8:21 am |
|
Frank Rizzo wrote:
> I am trying to get a default value for a column in a table (or a stored
> proc). To that effect, I am running sp_columns, which returns a
> column_def column and it is always NULL.
>
> I am also trying to get a default value for a stored proc parameter as
> well (running sp_sproc_columns) and the column_def is also always NULL.
>
> Am I missing something here? Adaptive Server Enterprise 12.5/SWR 9616
> on win2k.
>
> Regards
I get nulls too.. I see that the column was added to the output
of sp_columns between 11.5 and 12.0 - but I don't see the meaning of the
column documented anywhere. I don't know offhand if the catalog stored
procedures
are really supposed to return the default value or not.
I found a similar CR for sp_odbc_columns, CR 378641 which was fixed this
february.
On my 15.0 ASE, sp_odbc_columns does display the default value for the
column.
There is no corresponding CR for sp_columns, though.
You can get the information from the system tables, though.
You can get the object id of the default with this query:
Select cdefault from syscolumns where id = object_id("<object name>") and
name = <col or param name>
The source code for the default will be in syscomments (assuming the text
hasn't been hidden with sp_hidetext for
procedures):
select text from syscomments where id = <cdefault>
Or, putting it all together:
select text from syscomments c, syscolumns cl
where c.id = cl.cdefault
and cl.id = object_id("<object_name>")
and cl.name = <col_or_param_name>
In some cases this will be a literal value, in others it will be a function
or expression.
You can create a table with a nullable column of the appropriate type, bind
the
default to the column (using sp_bindefault), and insert a row to get a
sample value.
-bret
| |
| Frank Rizzo 2005-10-27, 8:21 am |
| Thank you. Your query is right on the money. However, getting back to
column_def. What is the status of this column? Is it a bug or reserved
for future use?
Regards
Bret Halford wrote:
>
> Frank Rizzo wrote:
>
>
>
>
> I get nulls too.. I see that the column was added to the output
> of sp_columns between 11.5 and 12.0 - but I don't see the meaning of the
> column documented anywhere. I don't know offhand if the catalog stored
> procedures
> are really supposed to return the default value or not.
>
> I found a similar CR for sp_odbc_columns, CR 378641 which was fixed this
> february.
> On my 15.0 ASE, sp_odbc_columns does display the default value for the
> column.
> There is no corresponding CR for sp_columns, though.
>
>
> You can get the information from the system tables, though.
>
> You can get the object id of the default with this query:
>
> Select cdefault from syscolumns where id = object_id("<object name>") and
> name = <col or param name>
>
> The source code for the default will be in syscomments (assuming the text
> hasn't been hidden with sp_hidetext for
> procedures):
>
> select text from syscomments where id = <cdefault>
>
> Or, putting it all together:
>
> select text from syscomments c, syscolumns cl
> where c.id = cl.cdefault
> and cl.id = object_id("<object_name>")
> and cl.name = <col_or_param_name>
>
> In some cases this will be a literal value, in others it will be a function
> or expression.
>
> You can create a table with a nullable column of the appropriate type, bind
> the
> default to the column (using sp_bindefault), and insert a row to get a
> sample value.
>
> -bret
>
| |
|
| Hi Frank,
That looks like the place for the default value on a default column. I find
this:
Nothing works with the OEM (DataDirect) drivers - the traditional. I tried
to get this implemented on ASE or through the vendor but we never had enough
push from any customer to get approval to get this done.
I find that on the newer ODBC Driver stuff, (different set of stored
procedures - there is a script in the sp subdirectory) and the
sp_odbc_columns will contain the column default value. However, its sproc
counterpart, sp_odbc_getprocedure
columns does not get teh parameter default
value - I am not sure why this is happening.
If you want to get this investigated please log a tech support case and we
can make a request to get this implemented on the new stuff.
-Paul
"Frank Rizzo" <none@none.net> wrote in message news:43458628@forums
-1-dub...[color=darkred]
> Thank you. Your query is right on the money. However, getting back to
> column_def. What is the status of this column? Is it a bug or reserved
> for future use?
>
> Regards
>
> Bret Halford wrote:
and[color=darkred]
text[color=darkred]
function[color=darkr
ed]
bind[color=darkred]
| |
| Frank Rizzo 2005-10-27, 8:21 am |
| Thanks. Probably not worth the trouble. I'll just use Bret's query
since it works reliably against the older versions of ASE as well.
Paul wrote:
> Hi Frank,
>
> That looks like the place for the default value on a default column. I find
> this:
>
> Nothing works with the OEM (DataDirect) drivers - the traditional. I tried
> to get this implemented on ASE or through the vendor but we never had enough
> push from any customer to get approval to get this done.
>
> I find that on the newer ODBC Driver stuff, (different set of stored
> procedures - there is a script in the sp subdirectory) and the
> sp_odbc_columns will contain the column default value. However, its sproc
> counterpart, sp_odbc_getprocedure
columns does not get teh parameter default
> value - I am not sure why this is happening.
>
> If you want to get this investigated please log a tech support case and we
> can make a request to get this implemented on the new stuff.
>
> -Paul
>
> "Frank Rizzo" <none@none.net> wrote in message news:43458628@forums
-1-dub...
>
>
> and
>
>
> text
>
>
> function
>
>
> bind
>
>
>
>
| |
|
| Hi Frank,
OK - that seems to be what everyone says :->
But the fact our new CSP sp_odbc_columns does lead me to believe we could do
this soemhow for sp_odbc_getprocedure
columns. I might pursue this and log a
CR anyway just for the record. It could be already on a list, but other
things got in the way.
Good luck.
-Paul
"Frank Rizzo" <none@none.net> wrote in message
news:4345ab37$1@foru
ms-2-dub...[color=darkred]
> Thanks. Probably not worth the trouble. I'll just use Bret's query
> since it works reliably against the older versions of ASE as well.
>
> Paul wrote:
find[color=darkred]
tried[color=darkred]
enough[color=darkred
]
sproc[color=darkred]
default[color=darkre
d]
we[color=darkred]
news:43458628@forums
-1-dub...[color=darkred]
stored[color=darkred
]
NULL.[color=darkred]
the[color=darkred]
this[color=darkred]
| |
| Frank Rizzo 2005-10-27, 8:21 am |
| > You can get the information from the system tables, though.
> You can get the object id of the default with this query:
>
> Select cdefault from syscolumns where id = object_id("<object name>") and
> name = <col or param name>
>
> The source code for the default will be in syscomments (assuming the text
> hasn't been hidden with sp_hidetext for
> procedures):
>
> select text from syscomments where id = <cdefault>
>
> Or, putting it all together:
>
> select text from syscomments c, syscolumns cl
> where c.id = cl.cdefault
> and cl.id = object_id("<object_name>")
> and cl.name = <col_or_param_name>
Bret,
Thanks, this works great for a table column but not for a stored
procedure parameter, since the cdefault field in syscolumns for the
sproc parameter always seems to be 0. Any ideas on how to retrieve the
sproc parameter default from the system tables?
Thanks
| |
| Bret Halford 2005-10-27, 8:21 am |
|
Frank Rizzo wrote:
>
> Bret,
>
> Thanks, this works great for a table column but not for a stored
> procedure parameter, since the cdefault field in syscolumns for the
> sproc parameter always seems to be 0. Any ideas on how to retrieve the
> sproc parameter default from the system tables?
>
> Thanks
Hi Frank,
It is looking like the only way to do so is to parse the sourcecode in
syscomments.
-bret
|
|
|
|
|