SYS.SQL_DEPENDENCIES, Refresh Dependencies

[bill]

Summary:
How can I ensure that sys.sql_dependencies is 100% up to date and
accurate prior to querying it for a list of dependencies?

Detail:
The table sys.sql_dependencies is very handy because you can query it
to find the tables and columns that are required by a given function,
procedure, etc.

The problem is that the dependency rows get trashed if you do this:

1. Create the referencED object
2. Create the referencING object
3. Drop the referencED object
4. Recreate the referencED object

After step 4, the referencING object will work, but the dependency
won't show up in the sys.sql_dependencies table.

If you ALTER the referencING object, the dependencies will show up
again. Is there some kind of completely innocuous version of the
ALTER statement I can issue for a function before I do my query? I
don't want to have to repeat the entire function definition.

Thanks,

Bill

[bill]

Found this in BOL: sp_refreshsqlmodule

This stored proc seems to work very well for my purposes (outlined in
my first message). I intend to use this procedure to refresh metadata
prior to querying for dependencies. Are there any "gotchas" of which
I should be aware?

Thanks,

Bill

BOL says "To refresh a view, you can use either sp_refreshsqlmodule or
sp_refreshview with the same results." To me, it sounds likew
sp_refreshview is redundant and I will never need to use it, do you
agee?

[Hugo Kornelis]

On Tue, 23 Jun 2009 00:21:34 -0700 (PDT), bill wrote:

>Found this in BOL: sp_refreshsqlmodule
>
>This stored proc seems to work very well for my purposes (outlined in
>my first message). I intend to use this procedure to refresh metadata
>prior to querying for dependencies. Are there any "gotchas" of which
>I should be aware?

Hi Bill,

If your stored procedures use dynamic SQL, then sp_refreshsqlmodule will
not be able to "see" those dependencies.

>BOL says "To refresh a view, you can use either sp_refreshsqlmodule or
>sp_refreshview with the same results." To me, it sounds likew
>sp_refreshview is redundant and I will never need to use it, do you
>agee?

I must admit that this is the first time I have ever heard about
sp_sqlrefreshsqlmodule. But after looking it up in Books Online, I'm
inclined to agree. The only advantage of sp_refreshview appears to be
that you save some wear and tear on the keyboard :)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Erland Sommarskog]

bill (billma...@gmail.com) writes:
> Found this in BOL: sp_refreshsqlmodule
>
> This stored proc seems to work very well for my purposes (outlined in
> my first message). I intend to use this procedure to refresh metadata
> prior to querying for dependencies. Are there any "gotchas" of which
> I should be aware?

Yes. If your procedure creates a temp table, and then later runs a query
with that temp table that also includes other table, those dependencies
will not be recorded. It does have to be a temp table - the important
thing it is a table that does not exist when the procedure is created/
refreshed. This the ugly misfeature known as deferred name resolution.

It is actually possible to get 100% accurate dependency information,
save dynamic SQL, but it takes a tool to get there. What I do is that
build an empty database from our scripts in SourceSafe, and my load tool
extracts all temp-table definitions and creates the temp table prior to
loading a procedure, and thereby killing deferred name resolution.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[bill]

Thanks both for your replies. I need to make sure I understand:

1. I have fortunately been able to avoid all dynamic SQL so I think
I'm safe on that front. I don't like dynamic SQL anyway, but the lack
of metadata is another reason to dislike it.
2. I don''t have any stored procs, but I do have table-valued UDF's
(both inline and multi statement). The UDF's happen to have only one
big SQLstatement (the one that returns the populated table variable).

I think #2 means Erland's warning about temp tables does not apply to
me, do you guys agree?

But to help me understand, let's say say I did have procedures that
made temp tables. My concerns have to do with promoting objects from
Dev to Test to Prod. So let's say I have stored proc
SP_TO_BE_PROMOTED that creates table #TEMP, and joins that table with
SOME_USER_TABLE to produce a result.

If I only promote SP_TO_BE_PROMOTED from Dev to Test and forget to
promote SOME_USER_TABLE, the stored proc won't work in Test. If I
understand Erland's warning correctly, sql_expressions_dependencies
won't say anything about needing SOME_USER_TABLE being required for
the procedure, because it is joined to a temporary table.

Is my understanding of Erland's warning correct? If so, would a
multi-statement table valued function that created a temp table as
part of its processing have the same issue? I have trouble seeing
ever wanting to make a temp table in a function, because if I am
returning so many rows that statistics matter, there might be better
ways to go(*). However, I would like to make sure i understand the
problem.

Thanks,

Bill

(*) Do you agree with this sentiment, or am I off base on this?

[Erland Sommarskog]

bill (billma...@gmail.com) writes:
> 2. I don''t have any stored procs, but I do have table-valued UDF's
> (both inline and multi statement). The UDF's happen to have only one
> big SQLstatement (the one that returns the populated table variable).
>
> I think #2 means Erland's warning about temp tables does not apply to
> me, do you guys agree?

Correct, because you cannot have temp tables in user-defined functions. You
can have table variables, but the problem does not exist with table
variables.

> But to help me understand, let's say say I did have procedures that
> made temp tables. My concerns have to do with promoting objects from
> Dev to Test to Prod. So let's say I have stored proc
> SP_TO_BE_PROMOTED that creates table #TEMP, and joins that table with
> SOME_USER_TABLE to produce a result.

You should not call your procedures sp_something, but I guess you know
that already. :-)

> If I only promote SP_TO_BE_PROMOTED from Dev to Test and forget to
> promote SOME_USER_TABLE, the stored proc won't work in Test. If I
> understand Erland's warning correctly, sql_expressions_dependencies
> won't say anything about needing SOME_USER_TABLE being required for
> the procedure, because it is joined to a temporary table.

sql_expression_dependencies is the new view for dependencies in SQL 2008,
and it does not have some of the problem that the old view has. For
instance, if you drop a table, the dependency information is still recorded.
And the problem with temp tables still remains. And there are a lot of
other problems as well. Overall, I think the new views were a mistake.

If you are using dependencies to see what you need to migrate from test
to dev, I think you should look into tools like SQL Compare from Red Gate.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx