June 8, 2006 at 1:05 pm
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.
June 8, 2006 at 1:52 pm
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://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=285845
N 56°04'39.16"
E 12°55'05.25"
June 9, 2006 at 5:36 am
You'd also have to search for JOIN...
June 9, 2006 at 6:06 am
and comma delimited list after the FROM in case the older style join structure is used: FROM TABLE1,TABLE2 , TABLE3
Lowell
June 9, 2006 at 8:56 am
...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.
June 9, 2006 at 9:04 am
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
June 9, 2006 at 2:09 pm
June 9, 2006 at 2:41 pm
Other suggestion from the simple side...
How about Show Execution Plan?
Or Set ShowPlan_Text = ON?
Mark
June 9, 2006 at 7:41 pm
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
June 10, 2006 at 6:12 am
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]
June 10, 2006 at 7:35 am
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
June 10, 2006 at 8:02 am
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://qa.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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply