May 18, 2010 at 1:04 am
Hi,
I am using SQL 2005.
I want to know, whether any option (SP/ Functions) exist in SQL to find out list of Tables used in an QUERY
Eg:
I have a query say 'SELECT * From Customer Join CustAddress ON Customer.CustomerId=CustAddress.CustomerId'
So i want find out what the the Tables Used in that query (in this case it is Customer and CustAddress'.
Do we have any Built in Option?
Thanks
R.Vasanth
May 18, 2010 at 2:02 am
Execution plan.......
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 18, 2010 at 7:03 am
Hi,
Thanks for your reply.
I think i dont explain my requirement
I want to get list of tables in C# coding and not in SSMS
May 18, 2010 at 9:48 am
You'll have to be a little more specific in your question. It is not clear what you are asking for.
It sounds like you want to call a SQL function or execute a SQL stored proc to which you can pass in a query string and have it tell you which tables it will use to execute the query?
If that's the case, I don't think you're going to find what you're looking for -- at least not pre-written. You'd have to write your own parser in C# or T-SQL.
May I ask WHY you need this functionality?
Rob Schripsema
Propack, Inc.
May 19, 2010 at 4:59 am
YES. you are correct. I want to know the list of Tables used in the Query.
The purpose is , while executing the Query, i need to add few more columns from the table (depends on the table used) to the Query
Eg: If my query is
Select Col1, Col2 From Table1
The Query to be executed and return the result is
Select Colxx, Col1, Col2 From Table1 `
May 19, 2010 at 5:13 am
Are you looking for dependent Tables in SP / Function?
if Yes,
select distinct *
from
(
SELECT sd.object_id object_id,
OBJECT_NAME(sd.object_id) OBJECT_NAME,
OBJECT_NAME(referenced_major_id) Ref_OBJECT_NAME, referenced_major_id Ref_OBJECT_Id
FROM sys.sql_dependencies sd
Where OBJECT_NAME(sd.object_id) = 'Your SP'
) a
where Ref_OBJECT_Id in
(
select object_id
from sys.tables
)
order by OBJECT_NAME
May 19, 2010 at 5:51 am
rvasanth
If this is more for documentational purposes, and not something you are trying to run live for every query, I think i have a potential solution. As already identified, the execution plan is what you need for on-the-run queries.
Save any query as a view, run sp_depends yourviewname and save the results, then drop the view.
sp-depends gives a lot of nice info about the actual table and column dependancies the view(saved query) is using; i think that might be what you want:
--example results:
name type updated selected column
dbo.TBSTATE user table no yes STATETBLKEY
dbo.TBSTATE user table no yes STATECODE
dbo.TBSTATE user table no yes STATENAME
dbo.TBCITY user table no yes CITYTBLKEY
dbo.TBCITY user table no yes DESCRIP
dbo.TBCITY user table no yes COUNTYTBLKEY
dbo.TBCOUNTY user table no yes COUNTYTBLKEY
dbo.TBCOUNTY user table no yes DESCRIP
dbo.TBCOUNTY user table no yes CODE
dbo.TBCOUNTY user table no yes STATETBLKEY
example code...you probably do not have the same tables, but it gets the idea accross:
CREATE VIEW VW_CITYCOUNTYSTATE
AS
SELECT TBCITY.CITYTBLKEY,
TBCITY.DESCRIP AS CITYNAME,
TBCOUNTY.COUNTYTBLKEY,
TBCOUNTY.DESCRIP AS COUNTYNAME,
TBCOUNTY.CODE AS COUNTYFIPS,
TBSTATE.STATETBLKEY,
TBSTATE.STATECODE,
TBSTATE.STATENAME
AS CITYDESCRIP
FROM TBCITY
LEFT JOIN TBCOUNTY ON TBCITY.COUNTYTBLKEY=TBCOUNTY.COUNTYTBLKEY
LEFT JOIN TBSTATE ON TBCOUNTY.STATETBLKEY=TBSTATE.STATETBLKEY
GO
exec p_depends VW_CITYCOUNTYSTATE
GO
drop view VW_CITYCOUNTYSTATE
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply