List tables used in a SP

Is there a way to list all tables used in a SP?
I have a very large DB that has a couple hunderd tables and I need to know
what tables are used by about 85 SP.
Thanks.
G.It's not precise, but check out sp_depends in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"George Lake" <gdlake@.gmail.com> wrote in message
news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
Is there a way to list all tables used in a SP?
I have a very large DB that has a couple hunderd tables and I need to know
what tables are used by about 85 SP.
Thanks.
G.|||I'd say sp_depends is less than precise. It's useless if it can't guarantee
to give you reliable info. SQL Server doesn't have any native support for
reliably tracking down this kind of dependencies.
Linchi
"Tom Moreau" wrote:
> It's not precise, but check out sp_depends in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "George Lake" <gdlake@.gmail.com> wrote in message
> news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>
>|||mmmm
any thrid party application that can do this?
The idea is to not "run" the SP, I cant have all the parameters for the SPs
Thanks.
G.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:65BE8D76-0C4B-40FD-9B83-F79BE2319259@.microsoft.com...
> I'd say sp_depends is less than precise. It's useless if it can't
> guarantee
> to give you reliable info. SQL Server doesn't have any native support for
> reliably tracking down this kind of dependencies.
> Linchi
> "Tom Moreau" wrote:
>> It's not precise, but check out sp_depends in the BOL.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> ..
>> "George Lake" <gdlake@.gmail.com> wrote in message
>> news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
>> Is there a way to list all tables used in a SP?
>> I have a very large DB that has a couple hunderd tables and I need to
>> know
>> what tables are used by about 85 SP.
>> Thanks.
>> G.
>>|||You could do something like :
select all tables from the db put them in a temporary table ,
using : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
and then create a cursor hitting
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tablename%'
AND ROUTINE_TYPE='PROCEDURE'
create a a recordset of all tables used in a sp . With a bit of added logic
if a record exists add to recordset
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"George Lake" <gdlake@.gmail.com> wrote in message
news:#QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>|||Although there isn't anything built in, here is what I do to identify if a
single table is used in stored procedures...
Generate a script for all of your stored procedures, and put it to a single
file. Search that file for the name of the table you are concerned with.
If you really need to know all the tables that are used, check "script all
dependent objects" when you generate your script. Every table that is
referenced should have a "create table" entry in the script.
note: if sp_depends doesn't always find dependencies, it is possible that
this method will miss dependencies as well. I have not used it on very
large databases, so I don't know if it will catch everything or not.
"George Lake" <gdlake@.gmail.com> wrote in message
news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>|||George,
this is pretty untested, but please give it a go:
select routine_name, table_name from information_schema.tables
cross join information_schema.routines
where routine_definition like '%' + table_name + '%'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||This is what I did recently to tackle this problem.
1. I ran a trace on particular long job.
2. Saved the Trace to a table
3. Retrieved a list of unique stored procedure calls
4. Generated a script for all the stored procedures with dependencies
on
5. Executed the script in new database.
I wound up coming up with a list of 104 tables for the 27 sprocs using
this method.
Then taking it furthur I looped through all those tables in a cursor
and ran sp_spacedused on each table Inserting the output into another
table.
I now had comlete stats on the tables, spaced used for a particlular
complex run in my application.
Andy
George Lake wrote:
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.|||This is what I did recently to tackle this problem.
1. I ran a trace on particular long job.
2. Saved the Trace to a table
3. Retrieved a list of unique stored procedure calls
4. Generated a script for all the stored procedures with dependencies
on
5. Executed the script in new database.
I wound up coming up with a list of 104 tables for the 27 sprocs using
this method.
Then taking it furthur I looped through all those tables in a cursor
and ran sp_spacedused on each table Inserting the output into another
table.
I now had comlete stats on the tables, spaced used for a particlular
complex run in my application.
Andy
George Lake wrote:
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.|||Short of a rebust T-SQL parser, there is no reliable, generally-applicable,
and automated way of identifying the tables used in a stored procedure.
But here's what I'd do, and it should cover most of the cases.
1. Script out the SP
2. Get a complete list of all the tables in the database
3. Write a little regular expression to look for each of the table name on
the list in the SP script. Make sure to look for 'complete' string that is
delimited either with whitespace or special characters.
You can get false positives from this, such as matching a string in the
comments or quoted string. But again, it should be very close.
Linchi
"George Lake" wrote:
> mmmm
> any thrid party application that can do this?
> The idea is to not "run" the SP, I cant have all the parameters for the SPs
> Thanks.
> G.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:65BE8D76-0C4B-40FD-9B83-F79BE2319259@.microsoft.com...
> > I'd say sp_depends is less than precise. It's useless if it can't
> > guarantee
> > to give you reliable info. SQL Server doesn't have any native support for
> > reliably tracking down this kind of dependencies.
> >
> > Linchi
> >
> > "Tom Moreau" wrote:
> >
> >> It's not precise, but check out sp_depends in the BOL.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Toronto, ON Canada
> >> ..
> >> "George Lake" <gdlake@.gmail.com> wrote in message
> >> news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> >> Is there a way to list all tables used in a SP?
> >> I have a very large DB that has a couple hunderd tables and I need to
> >> know
> >> what tables are used by about 85 SP.
> >>
> >> Thanks.
> >> G.
> >>
> >>
> >>
>
>