May 7, 2009 at 12:11 pm
Hi all,
Can anybody point me to a good script to catch all dependencies for stored procedures written in dynamic SQL ? I tried to search on this site as well as google but could not find any.
Thanks
May 7, 2009 at 12:16 pm
I am not aware of anything like that. In fact, since Dynamic SQL procedures' dependencies are presumably parameter-based, I have no idea how such a thing even could be written.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 7, 2009 at 1:16 pm
I have some idea how it could be written, but it's not easy to implement.
To parse each word from definition from sys.sql_modules and loop them against all objects in sys.sysobjects.
The most difficult part in it is to parse.
May 7, 2009 at 1:40 pm
SQL Guy (5/7/2009)
I have some idea how it could be written, but it's not easy to implement.To parse each word from definition from sys.sql_modules and loop them against all objects in sys.sysobjects.
The most difficult part in it is to parse.
Oh parsing it's hard all right. But the actual task itself is impossible. Never mind that this fits the definition of the impossible "Halting Problem", just consider this case:
Create proc spSelect @tablename as sysname AS
Declare @s-2 as NVarchar(MAX)
Select @s-2 = 'SELECT * From ' + @tablename
EXEC( @s-2 )
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 7, 2009 at 2:10 pm
When you pass a table name as an input parameter to dynamic SQL, it is really impossible. It is like to task DBA with find all parameter values ever passed to all stored procs, something like that.
But I have a different case, where a table name is encoded in a dynamic code, something like
If exists.....
set @sql = 'select from table_A'
else
set @sql = 'select from table_B'
etc....
exec(@sql)
For such a case I've created some code like this:
select
o.type,
s.name + '.' + o.name
from sys.sql_modules m
join sys.objects o on m.object_id = o.object_id
join sys.schemas s on o.schema_id = s.schema_id
where definition like '%MY_TABLE_NAME%'
But I need to loop against all tables, views in sys.object
May 7, 2009 at 2:15 pm
I confirm Barry. This seems to be impossible. The best sample was already supplied by Barry but what about comments? Constant text?
May 7, 2009 at 2:44 pm
SQL Guy (5/7/2009)
When you pass a table name as an input parameter to dynamic SQL, it is really impossible. ...
Right. Unfortunately what dynamic SQL is typically used for is to variablize column names, table names or both.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply