system view query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

system view query

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-12-14 : 05:12:05

Hi,

I want to read a list of procedure parameters and deault values from the system views, but they don't seem to be there I've tried the sql below but it doesn't give me any default values for the follwing procedure

Create PROCEDURE [dbo].[proc1]
(
@File as varchar(100),
@Adjust as bit,
@Raw as bit = 0,
@DateFrom as datetime = null,
@DateTo as datetime = null
)

select
so.name procname,
sp.name as paramname,
st.name +
case
when st.name like '%char' then '(' + cast(sp.max_length as varchar) + ')'
when st.name = 'decimal' then '(' + cast(sp.precision as varchar) + ',' + cast(sp.scale as varchar) + ')'
else ''
end as paramtype,
has_default_value,variable -- select *
from sys.all_objects so
left join sys.all_parameters sp
on sp.object_id = so.object_id
left join sys.systypes st
on st.xtype = sp.system_type_id
and st.name <> 'sysname'
where so.type = 'P'
and so.object_id > 0
and so.name = 'proc1'
order by so.name, sp.parameter_id
m

SELECT pr.name,default_value FROM sys.procedures p
INNER JOIN sys.parameters pr ON p.object_id = pr.object_id
WHERE p.name = 'proc1'

is there any other way I can retrieve these values.

thanks

Sean

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-14 : 08:59:19

the column_default value is ine here

Select * from information_schema.columns where column_default is not null

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources