Stored procedure dependancy on tables, views and other stored procedures 
Author Message
 Stored procedure dependancy on tables, views and other stored procedures
SQL Query Analyzer does a good job at showing the dependancies between
tables and views so that before you change a table or view you can look at
what will be affected, but it doesn't look like there is anyway to see what
tables and views stored procedures use without actually reviewing all of the
code for all of your stored procedures and checking for the names of all
tables and views used.

Are there any tools to help deal with this?



Sat, 25 Jun 2005 01:06:42 GMT
 Stored procedure dependancy on tables, views and other stored procedures

I created this procedure in pubs:

CREATE PROCEDURE foo
AS
BEGIN
 SELECT * FROM Authors
 EXEC reptq1
END
GO

Then ran this T-SQL:

SELECT
    DISTINCT OBJECT_NAME(depid)
    FROM sysdepends WHERE id=object_id('foo')

And the result was:

authors
reptq1

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


Quote:
> SQL Query Analyzer does a good job at showing the dependancies between
> tables and views so that before you change a table or view you can look at
> what will be affected, but it doesn't look like there is anyway to see
what
> tables and views stored procedures use without actually reviewing all of
the
> code for all of your stored procedures and checking for the names of all
> tables and views used.

> Are there any tools to help deal with this?



Sat, 25 Jun 2005 01:10:18 GMT
 Stored procedure dependancy on tables, views and other stored procedures
See the details about the stored procedure sp_depends & system
table sysdepends in SQL Server Books Online.

--
- Anith
(Please respond only to newsgroups)



Sat, 25 Jun 2005 01:13:44 GMT
 Stored procedure dependancy on tables, views and other stored procedures
Philip,

The tables must exist at the time the view is created, therefore dependency
information can be stored and is accurate. For sps however, delayed name
resolution is used... That means that you may create a stored procedure that
references objects which do not exist at the time the sp is created.
(Objects must exist at run time however.) If you create a sp that references
an object which does not yet exist, then there can not be a depency record
which points to that object, and therefore the sp_depends information will
not be accurate...  You can either come up with some sort of operational
procedure to create all of your stored procedures in a 'nice' order or some
folks simply export all of the code to a single flat file and search the
file for object names...

--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), C{*filter*}te, NC
www.computeredservices.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org

Quote:
> SQL Query Analyzer does a good job at showing the dependancies between
> tables and views so that before you change a table or view you can look at
> what will be affected, but it doesn't look like there is anyway to see
what
> tables and views stored procedures use without actually reviewing all of
the
> code for all of your stored procedures and checking for the names of all
> tables and views used.

> Are there any tools to help deal with this?



Sat, 25 Jun 2005 07:40:27 GMT
 Stored procedure dependancy on tables, views and other stored procedures
The problem is not during SP creation, but when developers make changes to
tables and or views. Is there an easy way to force the "compilation" of all
SP's to see if there are any missing columns or tables? It would be nice to
be able to catch simple errors like this.

We have been careful to not allow developers to access the DB except via
SP's in the hope that we could depend on SP compilation checks to catch any
problems when changes were made to the DB. This has worked well when we have
worked with other DB's but we can't seem to figure out a good way to perform
these checks using sqlserver.


Quote:
> Philip,

> The tables must exist at the time the view is created, therefore
dependency
> information can be stored and is accurate. For sps however, delayed name
> resolution is used... That means that you may create a stored procedure
that
> references objects which do not exist at the time the sp is created.
> (Objects must exist at run time however.) If you create a sp that
references
> an object which does not yet exist, then there can not be a depency record
> which points to that object, and therefore the sp_depends information will
> not be accurate...  You can either come up with some sort of operational
> procedure to create all of your stored procedures in a 'nice' order or
some
> folks simply export all of the code to a single flat file and search the
> file for object names...

> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), C{*filter*}te, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)

> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org


> > SQL Query Analyzer does a good job at showing the dependancies between
> > tables and views so that before you change a table or view you can look
at
> > what will be affected, but it doesn't look like there is anyway to see
> what
> > tables and views stored procedures use without actually reviewing all of
> the
> > code for all of your stored procedures and checking for the names of all
> > tables and views used.

> > Are there any tools to help deal with this?



Sun, 26 Jun 2005 01:43:45 GMT
 Stored procedure dependancy on tables, views and other stored procedures

Quote:
>> Is there an easy way to force the "compilation" of all SP's to

see if there are any missing columns or tables? <<

See sp_recompile in SQL Server Books Online. You can create a loop/
cursor & do it easily.

Another option is to use SHOWPLAN_TEXT which can create an estimated
exection plan for the stored procedure. The process will fail if you
have referred to a non-existing table/view in your stored procedure.
You can get the list of procedures from INFORMATION_SCHEMA.ROUTINES
view or from the sysobjects table. Here is one option. Do:

SELECT 'EXEC ' + ROUTINE_NAME
  FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE'
   AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') <> 1

Copy the resultset into your Query Analyzer with a SHOWPLAN_TEXT ON
before and SHOWPLAN_TEXT OFF after & just execute it. You will see a
list of errors, but with stored procedures with parameters this can
be a bit of work though.

--
- Anith
(Please respond only to newsgroups)



Sun, 26 Jun 2005 02:05:21 GMT
 Stored procedure dependancy on tables, views and other stored procedures
Is there a third party tool which shows SP dependancies for each table and
view?


Quote:
> >> Is there an easy way to force the "compilation" of all SP's to
> see if there are any missing columns or tables? <<

> See sp_recompile in SQL Server Books Online. You can create a loop/
> cursor & do it easily.

> Another option is to use SHOWPLAN_TEXT which can create an estimated
> exection plan for the stored procedure. The process will fail if you
> have referred to a non-existing table/view in your stored procedure.
> You can get the list of procedures from INFORMATION_SCHEMA.ROUTINES
> view or from the sysobjects table. Here is one option. Do:

> SELECT 'EXEC ' + ROUTINE_NAME
>   FROM INFORMATION_SCHEMA.ROUTINES
>  WHERE ROUTINE_TYPE = 'PROCEDURE'
>    AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') <> 1

> Copy the resultset into your Query Analyzer with a SHOWPLAN_TEXT ON
> before and SHOWPLAN_TEXT OFF after & just execute it. You will see a
> list of errors, but with stored procedures with parameters this can
> be a bit of work though.

> --
> - Anith
> (Please respond only to newsgroups)



Mon, 27 Jun 2005 03:11:12 GMT
 Stored procedure dependancy on tables, views and other stored procedures
You could write your own fairly easily, using INFORMATION_SCHEMA and
sysdepends...

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


Quote:
> Is there a third party tool which shows SP dependancies for each table and
> view?



> > >> Is there an easy way to force the "compilation" of all SP's to
> > see if there are any missing columns or tables? <<

> > See sp_recompile in SQL Server Books Online. You can create a loop/
> > cursor & do it easily.

> > Another option is to use SHOWPLAN_TEXT which can create an estimated
> > exection plan for the stored procedure. The process will fail if you
> > have referred to a non-existing table/view in your stored procedure.
> > You can get the list of procedures from INFORMATION_SCHEMA.ROUTINES
> > view or from the sysobjects table. Here is one option. Do:

> > SELECT 'EXEC ' + ROUTINE_NAME
> >   FROM INFORMATION_SCHEMA.ROUTINES
> >  WHERE ROUTINE_TYPE = 'PROCEDURE'
> >    AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') <> 1

> > Copy the resultset into your Query Analyzer with a SHOWPLAN_TEXT ON
> > before and SHOWPLAN_TEXT OFF after & just execute it. You will see a
> > list of errors, but with stored procedures with parameters this can
> > be a bit of work though.

> > --
> > - Anith
> > (Please respond only to newsgroups)



Mon, 27 Jun 2005 03:34:06 GMT
 Stored procedure dependancy on tables, views and other stored procedures
I want full featured tool, not just something I whip up and then hope all of
the developers use. For example, does Toad have that feature built in? It
would be great to have a tool that would warn people if they were about to
attempt to alter a table or view that SPs depended on and would make sure
that the table/views exist and have the correct column names.



Quote:
> You could write your own fairly easily, using INFORMATION_SCHEMA and
> sysdepends...

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> Please reply in the newsgroups, but if you absolutely
> must reply via e-mail, please take out the TRASH.



> > Is there a third party tool which shows SP dependancies for each table
and
> > view?



> > > >> Is there an easy way to force the "compilation" of all SP's to
> > > see if there are any missing columns or tables? <<

> > > See sp_recompile in SQL Server Books Online. You can create a loop/
> > > cursor & do it easily.

> > > Another option is to use SHOWPLAN_TEXT which can create an estimated
> > > exection plan for the stored procedure. The process will fail if you
> > > have referred to a non-existing table/view in your stored procedure.
> > > You can get the list of procedures from INFORMATION_SCHEMA.ROUTINES
> > > view or from the sysobjects table. Here is one option. Do:

> > > SELECT 'EXEC ' + ROUTINE_NAME
> > >   FROM INFORMATION_SCHEMA.ROUTINES
> > >  WHERE ROUTINE_TYPE = 'PROCEDURE'
> > >    AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') <> 1

> > > Copy the resultset into your Query Analyzer with a SHOWPLAN_TEXT ON
> > > before and SHOWPLAN_TEXT OFF after & just execute it. You will see a
> > > list of errors, but with stored procedures with parameters this can
> > > be a bit of work though.

> > > --
> > > - Anith
> > > (Please respond only to newsgroups)



Mon, 27 Jun 2005 04:33:05 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. temporary tables in stored procedures/parameter views vs stored procedures

2. Calling a Java Stored Procedure from another Java Stored Stored Procedure

3. Table or View Showing changes to Tables, Stored Procedures

4. View what stored procedures are currently in the procedure cache

5. Stored Procedures & Views - where are they stored

6. Call a stored procedure in another stored procedure

7. Calling a Stored Procedure from within a Stored Procedure

8. Using Resultset in Stored Procedure in another stored procedure

9. Stored Procedure calling Stored Procedure

10. Many Simple Stored Procedures VS. Few Complex Stored Procedures

11. Calling a Stored Procedure from within a Stored Procedure

12. Stored Procedure using another Stored Procedure


 
Powered by phpBB® Forum Software