February 27, 2014 at 1:23 pm
If anyone can provide script to list all the tables and columns involved in a Stored Procedure would be very helpful.
To add to this in the stored proc it is calling tables from other databases as well
February 27, 2014 at 2:11 pm
pretea (2/27/2014)
If anyone can provide script to list all the tables and columns involved in a Stored Procedure would be very helpful
This is a nearly impossible task to get 100% right. You can look at sys.sysdepends to get a good idea of the objects being referenced. It is difficult if not impossible to get all the columns though. Also, sys.sysdepends is not 100% accurate especially if you have dynamic sql in your code. Not to mention if your stored procedure calls another one, uses a function, view, etc...
To be fair, you will be more accurate doing this manually and it will likely be faster than trying to write a tool that will be totally accurate.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 27, 2014 at 2:23 pm
if possible can you provide script?
February 27, 2014 at 3:30 pm
pretea (2/27/2014)
if possible can you provide script?
Did you actually read my response? I would not even attempt to script this if accuracy is even a second thought. There are just too many things that are easy to miss.
Here is an example of on way you could do this. Remember this does NOT take into account dynamic sql or other levels of dependencies. If you reference a view that in turn has additional dependencies it would be missed with this. If you call other procedures their dependencies are not taken into account.
create table DependencyExample(ID int)
go
create table DependencyExample2 (id int)
go
create procedure DependencyProc as
select *
from DependencyExample
cross join DependencyExample2
go
select o.name as PrimaryObject,
o2.name as DependencyObject
from sys.sysdepends d
join sys.objects o on d.id = o.object_id
join sys.objects o2 on d.depid = o2.object_id
where id = OBJECT_ID('DependencyProc')
go
drop procedure DependencyProc
drop table DependencyExample
drop table DependencyExample2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2014 at 9:14 am
Thanks. But this does not help if I am the stored procedure is pulling information from tables in different database.
I am asking some automation method because there are 100's that I need to get information.
February 28, 2014 at 9:19 am
take a look at this DMV, and run this in the database you think has external references:
i think this view is supposed to populate with values, even if the object is not resolved in sysdepends/ does not exist as far as being an object.
select
object_schema_name(referencing_id) As SchemaName,
object_name(referencing_id) As ObjectName,
referenced_server_name,
referenced_database_name,
referenced_schema_name,
referenced_entity_name
from sys.sql_expression_dependencies
Lowell
February 28, 2014 at 9:40 am
Thanks, this works on 2008 R2 but I need for 2005 and functionality
sys.sql_expression_dependencies does not exist on 2005, what do you suggest?
February 28, 2014 at 9:44 am
Lowell (2/28/2014)
take a look at this DMV, and run this in the database you think has external references:i think this view is supposed to populate with values, even if the object is not resolved in sysdepends/ does not exist as far as being an object.
select
object_schema_name(referencing_id) As SchemaName,
object_name(referencing_id) As ObjectName,
referenced_server_name,
referenced_database_name,
referenced_schema_name,
referenced_entity_name
from sys.sql_expression_dependencies
Nice. I was unaware of this DMV.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2014 at 9:46 am
Not sure why I made my query so over complicated in my previous post. I guess it was near the end of the day and my brain had already shut down.
I realize this won't help the OP but for completeness sake I would offer this as a far simpler alternative.
select OBJECT_NAME(d.id) as PrimaryObject,
OBJECT_NAME(d.depid) as DependencyObject
from sys.sysdepends d
where id = OBJECT_ID('DependencyProc')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2014 at 9:58 am
pretea (2/28/2014)
Thanks, this works on 2008 R2 but I need for 2005 and functionalitysys.sql_expression_dependencies does not exist on 2005, what do you suggest?
nope.
in 2005, i believe only actual objects that were resolved are tracked.
there's a view, but it does not show anything from linked servers or other databases
select object_name(object_id) As ObjectName,* from sys.sql_dependencies
Lowell
February 28, 2014 at 9:58 am
Thanks Sean, but this does not give me column names.
Going back to my initial scenario. I have stored procedure created in one database but in the stored proc itself, it is selecting and inserting into tables to another database but in the same instance.
I would need all the tables, columns that stored procedure is using.
Thanks in advance.
February 28, 2014 at 10:08 am
pretea (2/28/2014)
Thanks Sean, but this does not give me column names.Going back to my initial scenario. I have stored procedure created in one database but in the stored proc itself, it is selecting and inserting into tables to another database but in the same instance.
I would need all the tables, columns that stored procedure is using.
Thanks in advance.
Neither of the ideas that Lowell or I provided even attempt to deliver the column names. They only provide the object names. Getting as granular as the columns involved is going to take manual effort. I know that isn't the answer your are looking for but there really isn't much option here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2014 at 10:36 am
to go that deep, you'd need to execute the procedure, capture the execution plan, and parse the xml out to get it down to the column level;
the queries in the proc could easily do SELECT * FROM LINKEDSERVER, and that would be resolved only at the isntant it was called.
Lowell
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply