Identifying stored procedure output columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In VS.Net Server Explorer, you can drill down to a SQL Server store procedure
and finally, it diplays not only parameter of the procedure but also the
result columns.

How can I replicate this behaviour in my application?
 
You can .. but ur gonna have to Query the master database.
Here are the relevant Queries -

Find out what databases exist in your Sql server -

SELECT
dtb.name AS [Database_Name],
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS
sysname),'''') + ']' + '/Database[@Name=' + quotename(dtb.name,'''') + ']'
AS [Database_Urn],
case when 1 = dtb.is_in_standby then 0x20 when 1 = dtb.is_cleanly_shutdown
then 0x40 else
case dtb.state when 1 then 0x1 when 2 then 0x4 when 3 then 0x2 when 4 then
0x8 when 5 then 0x80 when 6 then 0x10 else 0 end end AS [Database_Status],
CAST(has_dbaccess(dtb.name) AS bit) AS [Database_IsAccessible],
dtb.recovery_model AS [RecoveryModel],
dtb.user_access AS [UserAccess],
dtb.is_read_only AS [ReadOnly]
FROM
master.sys.databases AS dtb
WHERE
(dtb.database_id>4 and CAST(isnull(dtb.source_database_id, 0) AS bit)=0)
ORDER BY
[Database_Name] ASC

Find out what stored procs exist ---

SELECT
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS
sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']'
+ '/StoredProcedure[@Name=' + quotename(sp.name,'''') + ' and @Schema=' +
quotename(ssp.name,'''') + ']' AS [Urn],
sp.name AS [Name],
ssp.name AS [Schema],
CAST(sp.is_ms_shipped AS bit) AS [IsSystemObject]
FROM
sys.all_objects AS sp
INNER JOIN sys.schemas AS ssp ON ssp.schema_id = sp.schema_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(CAST(sp.is_ms_shipped
AS bit)=0)
ORDER BY
[Schema] ASC,[Name] ASC

Find out sproc. parameters including return values ----

SELECT
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS
sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']'
+ '/StoredProcedure[@Name=' + quotename(sp.name,'''') + ' and @Schema=' +
quotename(ssp.name,'''') + ']' + '/Param[@Name=' +
quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
baset.name AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') 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],
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(sp.is_ms_shipped AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.schemas AS ssp ON ssp.schema_id = sp.schema_id
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'uspGetBillOfMaterials' and ssp.name=N'dbo')
ORDER BY
param.parameter_id ASC


... As u might have guessed, I got these from Sql Server profiler, so you
might have to clean the queries a bit, but the logic is up there !!
How I generated these queries - Start profiler, and do what u'd normally do
in enterprise mgr. The profiler will catch all the relevant queries for u.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
Back
Top