🚀 go-pugleaf

�������ش����
From your description I understand you want to retrieve several information 
from a stored procedure through C# and/or SQL:
1. field list
2. database object which fields belong to
If I misunderstood your concern please correct me.

There are several methods that is similar to your requirements, you can 
choose what to use according to your concern:
1. sp_depends(sys.dm_sql_referencing_entities and 
sys.dm_sql_referenced_entities )
http://msdn.microsoft.com/en-us/library/ms189487.aspx
sample:
EXEC sp_depends @objname = N'testsp1' ;
EXEC sp_depends @objname = N'dbo.InsertTestData' ;

Result:
name type updated selected column
dbo.View1 view no yes col1
dbo.View1 view no yes col2
dbo.View1 view no yes col3

name type updated selected column
dbo.Table1 user table yes no col1
dbo.Table1 user table yes no col2

It shows the first stored procedure will do select and no update from a 
view dbo.View1, and related to three columns:col1, col2, col3.
And the second stored procedure will do update and no select to a user 
table dbo.Table1, and related to two columns:col1, col2


2. sp_sproc_columns
Returns column information for a single stored procedure or user-defined 
function in the current environment.

sample:
exec sp_sproc_columns @procedure_name= N'testsp1' ;

Result:
PROCEDURE_QUALIFIER PROCEDURE_OWNER PROCEDURE_NAME COLUMN_NAME COLUMN_TYPE 
DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS 
COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH 
ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
TestDB1 dbo testsp1;1 @RETURN_VALUE 5 4 int 10 4 0 10 0 NULL NULL 4 NULL 
NULL 0 NO 56

The information of this system stored procedure is much more than 
sp_depends...
You can get more information from sp_sproc_columns (Transact-SQL) 
<http://technet.microsoft.com/en-us/library/ms182705.aspx>

3. sp_helptext
http://msdn.microsoft.com/en-us/library/ms176112.aspx
Displays the definition of a user-defined rule, default, unencrypted 
Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, 
computed column, CHECK constraint, view, or system object such as a system 
stored procedure.

sample:
sp_helptext 'testsp1'

4. SET FMTONLY (Transact-SQL) 
http://msdn.microsoft.com/en-us/library/ms173839.aspx
Returns only metadata to the client. Can be used to test the format of the 
response without actually running the query.

sample:
SET FMTONLY ON;
GO
exec testsp1;
GO
SET FMTONLY OFF;
GO

 �� �� ZAKU (Dem ����ɫ�н�����
) �Ĵ������ᵽ: ��: exec sp_sproc_columns @procedure_name= N'InsertTestData' ;
: ����ǰ������������ʹ����Ƶ�����
: �� �� loveforyou (��Զ����) �Ĵ������ᵽ: ��
: : ���ĸ�ϵͳ�����ͼ���а���
: : sys.all_parametersֻ��һ��is_output��û��˵����ֵ����
: : sql server ���
: : ��̴����ǡ�


--
http://bbs.sjtu.edu.cn/file/comic/122390669464913.jpg

�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 207.46.55.28]