how to parse a T-SQL script to get the tables touched by the query?

Question

how to parse a T-SQL script to get the tables touched by the query?

Jennifer Wang-317627

Old Hand

Points: 359
More actions
June 8, 2006 at 1:05 pm

#366374

I would like to know if there is any way I can get the tables affected by any T-SQL Script (select, update, delete, insert)? If there is any existing script written to parse this it will be great. Note it should also take care of things like subqueries.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Maybe parsing the query and searching for "FROM " / "UPDATE " / "INSERT INTO " / "INSERT " text, and then a space after that, should give a table name. Repeat until query no more...

Also look at this link http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=285845

N 56°04'39.16"
E 12°55'05.25"

Marshall Smith SSCrazy Points: 2041 More actions · Answer 2

Marshall Smith

SSCrazy

Points: 2041

June 9, 2006 at 5:36 am

#642748

You'd also have to search for JOIN...

Lowell SSC Guru Points: 323518 More actions · Answer 3

and comma delimited list after the FROM in case the older style join structure is used: FROM TABLE1,TABLE2 , TABLE3

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David McFarland SSChampion Points: 11815 More actions · Answer 4

...and SELECT INTO and BULK INSERT, and for that matter, your script might create TRIGGERS on a table, and probably a million other things.

Another possibility would be to grab a list of tables from the INFORMATION_SCHEMA.Tables view (yes, you can cheat and use sysobjects, but Bill Gates will wag his finger at you if you do) and look for those strings in your script.

That won't catch newly created tables inside your script, but you can look for the much smaller set of keywords that are used to create tables as a second pass.

Lowell SSC Guru Points: 323518 More actions · Answer 5

I like David McFarlands idea; just looping thru the text of the view/stored proc/whatever object, looking for specific strings is MUCH better than trying to write a dynamic string reader to look for strings that occur after other strings, that might or might not be findable due to spacing, CrLf or other alis names

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

CK Bhatia Say Hey Kid Points: 679 More actions · Answer 6

This may sound too simplistic, but why not create the script as a dummy procedure and then do a "Display Dependencies..." on this procedure?

I feel the need - the need for speed

CK Bhatia

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 7

Other suggestion from the simple side...

How about Show Execution Plan?

Or Set ShowPlan_Text = ON?

Mark

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

This will not find the tables affected by calls to other procs within the proc identified as @objname... but it may do what you ask... and, you get some column info to boot...

USE Northwind

GO

EXEC dbo.sp_depends @objname = 'Ten Most Expensive Products'

You could run the result set into a table or temp table for additional processing.

See Books Online for additional information on sp_Depends and the sysDepends table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

This is one I found a long time ago here in this forum ... but can not remember the true authors name

CREATE PROCEDURE UDP_FindWordsInaSP

@Watchword varchar(50)

AS

SELECT distinct

'type' = case type

when 'FN' then 'Scalar function'

when 'IF' then 'Inlined table-function'

when 'P' then 'Stored procedure'

when 'TF' then 'Table function'

when 'TR' then 'Trigger'

when 'V' then 'View'

end,

o.[name],

watchword = @Watchword

FROM dbo.sysobjects o (NOLOCK)

JOIN dbo.syscomments c (NOLOCK)

ON o.id = c.id

WHERE charindex(lower(@Watchword),lower(text)) <> 0

and o.type in ('FN', 'IF', 'P', 'TF', 'TR', 'V')

and o.name NOT LIKE 'dt%' and o.name NOT LIKe 'sys%'

and o.name NOT LIKE 'UDP_FindWordsInaSP'

ORDER BY type, o.[name]

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

Finally found another procedure which might be easier to use

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

/****** Object: Stored Procedure dbo.SPWhichAccessATable Script Date: 6/10/2006 9:32:09 AM ******/

CREATE PROCEDURE Dbo.SPWhichAccessATable

@TableName VARCHAR(128)

AS

SELECT so.name as 'Table', sob.name as 'Stored Procedure name'

FROM sysobjects so

LEFT OUTER JOIN (sysobjects sob left outer join sysdepends on sob.id = sysdepends.id) on sysdepends.depid = so.id

WHERE so.xtype = 'u' AND sob.xtype = 'p' AND so.name = @TableName

GROUP BY so.name, sob.name ORDER BY so.name

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

stax68 SSChampion Points: 11711 More actions · Answer 11

That's to find the stored procs that access a given table. I expect the results would contain a lot of duplicates. Note also that sysdepends is not reliable for sps. See notes on this in the post linked above:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=285845

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant