Query to return all tables in db

Question

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 13-Sep-12 18:34pm

lalitkr

Add a Solution

Comments

AmitGajjar 14-Sep-12 1:05am

what you mean by all tables? tabledata/tablestructure ?

3 solutions

Solution 1

SQL

SELECT Name
FROM sys.Tables

Posted 13-Sep-12 18:41pm

Kuthuparakkal

Solution 2

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

Posted 13-Sep-12 19:07pm

lalitkr

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

_Amy · Accepted Answer · 2012-09-13T19:30:00

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