Click here to Skip to main content
15,920,383 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I need a query to return all the tables when i execute all the stored procedure in one go .

Suppose i have 5 tables
and 2 sp associated with the two tables among five tables

now i need a query when i execute sp1 and sp2 it return all the tables , can anybody have any idea ?
Posted
Comments
AmitGajjar 14-Sep-12 1:05am    
what you mean by all tables? tabledata/tablestructure ?

Hi,
Are you looking for something like:
SQL
WITH ProcTablesDetails AS (
SELECT o.name AS Procedure_Name, oo.name AS Table_Name,
ROW_NUMBER() OVER(PARTITION BY o.name,oo.name ORDER BY o.name,oo.name) AS Details
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT Procedure_Name, Table_Name FROM ProcTablesDetails
WHERE Details = 1
ORDER BY Procedure_Name,Table_Name



All the best.
--Amit
 
Share this answer
 
SQL
SELECT Name
FROM sys.Tables
 
Share this answer
 
Thanks for reply , but it is not i am looking for ....

I want to execute like

exec spname1 , spname2 , spname3
GO

On executing above statement it return

sp_name Table_name

spname1 table3

spname2 table1

spname2 table4
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900