need to make up a list of tables used by a very big stored procedure, but
was hoping to avoid having to do this chore manually.This is a multi-part message in MIME format.
--=_NextPart_000_008E_01C37B6D.3F97CAB0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You can use sp_depends but it's not entirely reliable.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jed Ozone" <jed ozone@.yahoo.com> wrote in message =news:#Pzj734eDHA.1764@.TK2MSFTNGP09.phx.gbl...
Is there any to get a list of tables used in a given stored procedure? =I
need to make up a list of tables used by a very big stored procedure, =but
was hoping to avoid having to do this chore manually.
--=_NextPart_000_008E_01C37B6D.3F97CAB0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You can use sp_depends but it's not =entirely reliable.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jed Ozone"
--=_NextPart_000_008E_01C37B6D.3F97CAB0--|||Agree with Tom here ... You maynot be able to get the tables referenced in
the D-SQL portion under such circumstances ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"Jed Ozone" <jed ozone@.yahoo.com> wrote in message
news:%23Pzj734eDHA.1764@.TK2MSFTNGP09.phx.gbl...
> Is there any to get a list of tables used in a given stored procedure? I
> need to make up a list of tables used by a very big stored procedure, but
> was hoping to avoid having to do this chore manually.
>|||This point to a Google search result of three recent threads that cover this
issue: http://tinyurl.com/nemn
"Jed Ozone" <jed ozone@.yahoo.com> wrote in message
news:#Pzj734eDHA.1764@.TK2MSFTNGP09.phx.gbl...
> Is there any to get a list of tables used in a given stored procedure? I
> need to make up a list of tables used by a very big stored procedure, but
> was hoping to avoid having to do this chore manually.
>|||If sp_depends isn't returning the right details, try dropping and
re-creating your procedures.
"hemol" <jedozone.remove@.yahoo.com> wrote in message
news:VCj9b.464605$uu5.79581@.sccrnsc04...
Thanks for the suggestion. Unfortunately, it didn't work for me (only
returned information on about 1% of the objects in the stored procedure). I
guess my options look like doing this manually or writing a dirty SQL
parser. Thanks again.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uwKWK74eDHA.616@.tk2msftngp13.phx.gbl...
You can use sp_depends but it's not entirely reliable.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jed Ozone" <jed ozone@.yahoo.com> wrote in message
news:#Pzj734eDHA.1764@.TK2MSFTNGP09.phx.gbl...
Is there any to get a list of tables used in a given stored procedure? I
need to make up a list of tables used by a very big stored procedure, but
was hoping to avoid having to do this chore manually.|||A quick and dirty way to do this is to script out all of the SPs, in SEM
right click your database -> All Tasks->Generate SQL Script.
Then open the script in an editor and do a find...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jed Ozone" <jed ozone@.yahoo.com> wrote in message
news:#Pzj734eDHA.1764@.TK2MSFTNGP09.phx.gbl...
> Is there any to get a list of tables used in a given stored procedure? I
> need to make up a list of tables used by a very big stored procedure, but
> was hoping to avoid having to do this chore manually.
>|||Just tried that (after you suggestion) but still it won't give me an
accurate list of the tables. Thanks for the suggestion though.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uU%2340N5eDHA.2252@.TK2MSFTNGP12.phx.gbl...
> If sp_depends isn't returning the right details, try dropping and
> re-creating your procedures.
>
>
> "hemol" <jedozone.remove@.yahoo.com> wrote in message
> news:VCj9b.464605$uu5.79581@.sccrnsc04...
> Thanks for the suggestion. Unfortunately, it didn't work for me (only
> returned information on about 1% of the objects in the stored procedure).
I
> guess my options look like doing this manually or writing a dirty SQL
> parser. Thanks again.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uwKWK74eDHA.616@.tk2msftngp13.phx.gbl...
> You can use sp_depends but it's not entirely reliable.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Jed Ozone" <jed ozone@.yahoo.com> wrote in message
> news:#Pzj734eDHA.1764@.TK2MSFTNGP09.phx.gbl...
> Is there any to get a list of tables used in a given stored procedure?
I
> need to make up a list of tables used by a very big stored procedure,
but
> was hoping to avoid having to do this chore manually.
>
>|||The tables in the SP are contained in the SELECT part of INSERT INTO
statements. Many of these statements do use variables that are passed into
the stored procedure (e.g., WHERE SomeDate between @.start and @.end) and some
do joins with temp tables (that are also created within the stored
procedure).
There are a few EXEC statements in the beginning, but only to call
additional stored procedures (which I don't care about). No tables are
accessed in this manner. Some complexity in the stored procedure seems to
make SQL Server (2000) not be able to track dependents for this beast.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:Odvnmt5eDHA.2252@.TK2MSFTNGP12.phx.gbl...
> > Just tried that (after you suggestion) but still it won't give me an
> > accurate list of the tables. Thanks for the suggestion though.
> Is it possible that some of the tables are referenced within dynamic SQL
> (e.g. EXEC('...') or EXEC sp_executesql '...')?
>
>
No comments:
Post a Comment