(gi******@talk21.com) writes:
Just to clarify - AbaPerls installs the stored procedures in some
random order,
Actually alphabetic.
then installs them a second time to get the dependencies correct?
It was the intention, but:
CREATE PROCEDURE a_sp AS
EXEC b_sp
go
CREATE PROCEDURE b_sp AS
SELECT 12
go
CREATE PROCEDURE c_sp AS
EXEC b_sp
go
ALTER PROCEDURE a_sp AS
EXEC b_sp
go
ALTER PROCEDURE b_sp AS
SELECT 12
go
ALTER PROCEDURE c_sp AS
EXEC b_sp
go
EXEC sp_depends b_sp
EXEC sp_depends a_sp
The output is:
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'b_sp'. The stored procedure will still be
created.
In the current database, the specified object is referenced by the
following:
name type
------------ -----------------
dbo.c_sp stored procedure
Object does not reference any object, and no objects reference it.
The reason for this is that SQL Server drops the dependencies when you say
ALTER PROCEDURE. (Fixed in SQL 2005.)
There is still a point with loading procedures twice, though: on the
second time round, you don't get the "add rows to sysdepends" message.
AbaPerls includes a tool LISTERRS which reads a log from DBBUILD, and
LISTERRS skips the first run of procedures. This means that all "add
rows to sysdepends" messages it lists are for real.
In any case, even if SQL Server did it right it would not be sufficient
for us in many cases, since many references are from client code. When
I need to know whether a stored procedure is referenced somewhere, I
run another AbaPerls tool: SSGREP. This tool searches one or more
SourceSafe as defined from a config file for one or more Perl regexps.
By default, SSGREP only searches code and excludes comments. If I like,
I can restrict the change by file type of language. Output can be plain
text or HTML. (The HTML does not have any links, it was just that HTML
was an easy way to bring highlighting into the output.)
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp