Does this give you what you need?

-- stored procedures and their parameters
SELECT 
	r.specific_name AS [storedProcedure]
	, p.parameter_name AS [parameter]
	, p.data_type AS [datatype]
	, p.parameter_mode AS [direction]
FROM 
	information_schema.routines r
	INNER JOIN information_schema.parameters p ON p.specific_name = r.specific_name
ORDER BY
	r.specific_name,
	p.ordinal_position

-- stored procedures and the tables that they reference
SELECT DISTINCT
	object_name(id) AS [storedProcedure]
	,object_name(depid) AS [referencedTable]
FROM 
	sysdepends 
WHERE 
	OBJECTPROPERTY(id, 'IsProcedure') = 1
	AND OBJECTPROPERTY(depid, 'IsUserTable') = 1

Just realize that the dependency information is only as valid as the sysdepends table. SQL Server does not force this table to be up to date. You can create a stored procedure that references a table which does not exist. In this situation, SQL Server will not create a row in sysdepends. When the table is created, SQL Server will NOT add the row to sysdepends until the stored procedure is recreated.

-Mike


>I created a script to give me the info I needed for my stored proc documentation. When this is run in QA, it returns two sets of records:
>
>1. A list of the stored procedures and dependent objects
>2. A list of stored procedures and the associated parameters (and datatypes of the params).
>
>There may be an easier way to do this - but it gives me the info I was looking for...
>
>(Note: All my stored procedures start with "up_" - you will have to make to change variable "@Prefix" if your sp's have different naming convention)
>
>Here is the code:
>
>

>
>declare  @CurSP cursor,   --cursor to hold stored procedures
>	 @CurTableDepend cursor,  -- cursor to hold dependent objects
>	 @CurSPParams cursor,  -- cursor to hold parameters
>	 @SPNameVC varchar(100),
>	 @ObjectName varchar(100),
>	 @ObjectType varchar(3),
>	 @ObjectTypeName varchar(25),
>	 @ParamName varchar(100),
>	 @DataType varchar(100),
>	 @Prefix varchar(15),
>	 @InOut char(8),
>	 @ObjectID integer,
>	 @DependID integer,
>	 @ColStat smallint,
>	 @Length smallint,
>	 @Precision tinyint,
>	 @Scale tinyint
>		
>
>declare @TableSPParams table
>	(
>	StoredProcNameVC varchar(100),
>	ParamaterNameVC varchar(100),
>	ParameterTypeCH char(6),	
>	ParamaterDataTypeVC varchar(25),
>	Length smallint,
>	NumericPrecision tinyint,
>	NumericScale tinyint
>	)
>
>declare @TableSPTableDepends table
>	(
>	StoredProcName varchar(100),
>	DependentObjName varchar(100),
>	DependentObjType varchar(25)	
>	)
>
>-- open a cursor containing stored procedure names
>-- and id numbers from the sysobjects table
>
>set @Prefix = 'up_%'
>
>SET @CurSP  = cursor for
>
>select name, id from sysobjects
>where xtype = 'P' and
>name like @Prefix
>order by name
>
>open @CurSP
>
>Fetch next from @CurSP
>Into @SPNameVC, @ObjectID
>
>-- loop through the cursor containing stored procedure names
>-- and Id's and get seperate cursors containing the names of
>-- dependant tables as well as a cursor contining all the
>-- parameters of the stored procedure
>
>While (@@Fetch_Status = 0)
>
>Begin
>
>	-- create a cursor with one column - a list of objects that the
>	-- stored procedure is dependent on  (i.e. tables, other stored procs,
>	-- UDF's required by the stored procedure)
>	SET @CurTableDepend  = cursor for
>	select depid from sysdepends where id = @ObjectID  group by depid
>		
>	open @CurTableDepend
>	
>	Fetch next from @CurTableDepend
>	Into @DependID
>
>	-- loop through the cursor and get the dependent object name and type
>	While (@@Fetch_Status = 0)
>	
>	Begin
>
>		set @ObjectName = (select name from sysobjects where id = @DependID)
>		set @ObjectType = (select xtype from sysobjects where id = @DependID)
>	
>		set @ObjectTypeName =
>	
>		Case  @ObjectType
>			
>			when 'U' then 'Table'
>			when 'P' then 'Stored Procedure'
>			when 'FN' then 'User Defined Function'
>			else 'N/A'
>		
>		end
>		
>		insert into @TableSPTableDepends values
>					(
>					@SPNameVC,
>					@ObjectName,
>					@ObjectTypeName
>					)
>		
>		Fetch next from @CurTableDepend
>		Into @DependID
>
>	End
>
>	--------------------------
>	--------------------------
>
>	-- create a cursor from syscolumns and systypes system tables
>	-- which will contain the stored procedure parameters, along
>	-- with their datatypes
>	SET @CurSPParams = cursor for
>	
>	SELECT cast(syscolumns.name as varchar(100))  AS ParamName,
>		syscolumns.colstat as ColStat,
>		syscolumns.length  as Length,
>		syscolumns.xprec  as NumericPrecision,
>		syscolumns.xscale as NumericScale,
>	 	cast(systypes.name as varchar(100)) as DataType
>	FROM syscolumns INNER JOIN
>	   systypes ON syscolumns.xtype = systypes.xtype
>	WHERE (syscolumns.id = @ObjectID )
>		
>	open @CurSPParams
>	
>	Fetch next from @CurSPParams
>	Into @ParamName, @ColStat, @Length, @Precision, @Scale, @DataType
>	--Fetch next from @CurSPParams
>	--Into @ParamName, @Length, @Precision, @Scale, @DataType
>
>	-- loop through the cursor and get the dependent object name and type
>	While (@@Fetch_Status = 0)
>	
>	Begin
>
>		set @InOut =
>		
>			Case  @ColStat
>			
>			when 0 then 'Input'
>			when 4 then 'Output'
>			else ''
>		
>			end
>		
>
>		
>		insert into @TableSPParams values
>					(
>					@SPNameVC,
>			 		@ParamName,
>					@InOut,
>					@DataType,
>					@Length,
>					@Precision,
>					@Scale
>					)
>		
>
>		Fetch next from  @CurSPParams
>		Into @ParamName, @ColStat, @Length, @Precision, @Scale, @DataType
>
>	End
>	
>
>
>
>Fetch next from @CurSP
>Into @SPNameVC, @ObjectID
>
>end
>
>
>close @CurSP
>Deallocate @CurSP
>
>close @CurTableDepend
>Deallocate @CurTableDepend
>
>close @CurSPParams
>Deallocate @CurSPParams
>
>select * from @TableSPTableDepends order by StoredProcName
>
>select * from @TableSPParams
>
>

>
>>Hi Mike,
>>
>>Thanks for the tip - that was the first place I looked - although I couldn't seem to find what I was looking for this time. (I have found a number of excellent, time saving scripts at that site in the past.) I'll try to roll my own and see what I can accomplish.
>>
>>Al
>>
>>
>>
>>>See if there's anything here that helps: http://www.swynk.com/
>>>
>>>-Mike
>>>
>>>>Hi,
>>>>
>>>>I have to do some documentation on one of our systems that has over 100 stored procedures (excluding the system SP's).
>>>>
>>>>I'd like to write a query that will give the the SP name, table dependencies, input/output parameters and datatypes.
>>>>
>>>>My question is this: Should I query the system tables directly or use the Information Schema Views? If anyone already has a query that generates results similiar to what I'm looking for - would it be possible to post the query you used?
>>>>
>>>>Thanks,
>>>>
>>>>Al