August 19, 2010 at 2:54 am
Let take Example:
I have below SQL Statement it’s just an example Statement might be complex.
I want to separate out Table list used in SQL Statement.
SQL STATEMENT:
SELECT TU.UserID,TU.UserName,TUR.RoleID,TR.RoleName
FROM TBL_Users TU
INNER JOIN TBL_UserRoles TUR ON TU.UserID = TUR.UserID
INNER JOIN TBL_Roles TR ON TUR.RoleID = TR.RoleID
RESULT :
TBL_Users
TBL_UserRoles
TBL_Roles
Can we do this? Any IDIA?
August 19, 2010 at 3:17 am
Jayraj,
Try below script.
select name from sysobjects where id in (select sd.depid from sysobjects so, sysdepends sd
where so.name = 'My_DownloadComplains' and
sd.id = so.id )
Hope this may help u.
August 19, 2010 at 3:40 am
Hi ,
Thanks for reply,
but I Didnt understand how can i pass the sql Statment to your Query?
Thanks Jayraj
August 19, 2010 at 3:54 am
Hi,
If possible, then convert yr query into stored procedure or make Views.
Otherwise it can't help u...
But what is yr actual requirement?
August 19, 2010 at 4:11 am
I have set of SQL Statement
And I want to know which tables used in particular SQL Statement.
For Example I have one select Query like “Select * From TBL_Users”
So from that query I want a table name means TBL_Users as out put.
Hope u understand the requirement.
August 19, 2010 at 6:07 am
Jayraj.Todkar there is no easy way to do this, and especially not easy in SQL itself.
I can think of two ways to do this:
get the execution plan as xml.
inside that xml, you'll see the group "OutputList:
for example:
SELECT * From TallyCalendar:
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[TallyCalendar]" Column="TheDate" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[TallyCalendar]" Column="DayOfWeek" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[TallyCalendar]" Column="IsHoliday" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[TallyCalendar]" Column="IsWorkHoliday" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[TallyCalendar]" Column="IsDaylightSavings" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[TallyCalendar]" Column="HolidayName" />
</OutputList>
note how it has every table and column in it, so you could parse that for the value....but it's not easy to ge thte execution plan in SQL;
in a programming language
you could use a regular expression to find everything between "from" and "WHERE"|end of statment
and figure out how to parse all the tables after keywords like JOIN | FROM; not easy.
there's a difference between the objects in the query and the underlying dependancies.
in the same xml above, there is another group <DefinedValues>, which has the sources for the outputlist from above...sometimes they are the smae, other times they are different...aliased columns, sourced from views or calculations....
if you SELECT * FROM SOMEVIEW, the view may reference multiple tables...
Lowell
August 19, 2010 at 6:49 am
Dear Lowell
Thanks for Reply
No Need to Check table referance used in view. if we get view name thats sufficent.
can we do somthing like ...
1. Get the Object list from Database
2. Find the Each Object of above list into SQL Query.
I think it will work but this solution might be not genric, but let me check ...
i will get back to you with this solution.
please some Suggetion on above solution.
Jayraj
August 19, 2010 at 7:13 am
well i am not na expert in xml by any means, but i googled a bit and got this to almost work; this might give you some ideas:
--the query to test against
SELECT * FROM TallyCalendar
Declare @xml XML
SELECT
qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/OutputList/ColumnReference/@Database)[1]' , 'varchar(100)') AS [DATABASE] ,
qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/OutputList/ColumnReference/@Table)[1]' , 'varchar(100)') AS
,
qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/OutputList/ColumnReference/@Column)[1]' , 'varchar(100)') AS [COLUMN]
From sys.dm_exec_requests
Cross Apply sys.dm_exec_query_plan (plan_handle) qp
Where session_id = @@spid
Lowell
August 19, 2010 at 8:39 am
Using Books On Line - "sys.sql_expression_dependencies (Transact-SQL) "
Here is a sample T-SQL statement that will return the "Referenced Schema Name" and "Referended entity name" (table name)
USE AdventureWorks2008;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,
o.type_desc AS referencing_desciption,
COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,
referencing_class_desc, referenced_class_desc,
referenced_server_name, referenced_database_name, referenced_schema_name,
referenced_entity_name,
COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'your stored procedure name');
Of course the above could be simplified as:
USE AdventureWorks2008;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,
o.type_desc AS referencing_desciption,
referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'Your stored procedure name');
August 19, 2010 at 10:21 am
If you do not already have your query as a stored procedure you can simply create a temporary procedure, like this:
use AdventureWorksLT2008
go
declare @sql varchar(max)
-- Assume this is the query you want to extract references from
set @sql='
SELECT
p.[ProductID]
,p.[Name]
,pm.[Name] AS [ProductModel]
,pmx.[Culture]
,pd.[Description]
FROM [SalesLT].[Product] p
INNER JOIN [SalesLT].[ProductModel] pm
ON p.[ProductModelID] = pm.[ProductModelID]
INNER JOIN [SalesLT].[ProductModelProductDescription] pmx
ON pm.[ProductModelID] = pmx.[ProductModelID]
INNER JOIN [SalesLT].[ProductDescription] pd
ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
exec dbo.uspLogError
select * from SalesLT.vGetAllCategories
update SalesLT.Address set city=city+''x''
'
-- create a temporary stored procedure using the supplied query
set @sql = 'create procedure temp$$ as '+@sql
exec (@sql)
-- extract all dependencies
select sed.referenced_schema_name, sed.referenced_entity_name, so.type_desc
from sys.sql_expression_dependencies sed
join sys.objects so on sed.referenced_id=so.object_id
WHERE referencing_id = OBJECT_ID(N'temp$$');
-- drop the temporary procedure
drop procedure temp$$
The code above returns the following:
referenced_schema_namereferenced_entity_nametype_desc
SalesLT Address USER_TABLE
SalesLT Product USER_TABLE
SalesLT ProductDescription USER_TABLE
SalesLT ProductModel USER_TABLE
SalesLT ProductModelProductDescription USER_TABLE
dbo uspLogError SQL_STORED_PROCEDURE
SalesLT vGetAllCategories VIEW
If you are only interested in references to tables you can obviously filter on type_desc
/SG
August 19, 2010 at 11:02 pm
Dear Friend Stefan_G
Thanks for Solutions. Too Good
Regards,
Jayraj
August 19, 2010 at 11:04 pm
Hi bitbucket-25253
Its really Gr8 Solution. Thank you
Regards,
Jayraj
August 20, 2010 at 3:32 pm
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply