How important are dependencies?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ambradnum@hotmail.com - 18 Jul 2008 11:31 GMT

I have about 1000 views, functions and stored procedures in my
database. They are encrypted for security reasons - my database is
used by a number of clients. I have all the scripts held in separate
files on my PC.

If I need to change one of them, their dependencies are lost. I
cannot use the Generate Scripts option in Enterprise Manager, because
the objects are encrypted.

The only way I know of to get the dependencies back is to search
through all the views, functions and stored procedures for the changed
one and then "follow the chain" and recreate all the relevant objects
in order.

For example
ViewA is called by FunctionB which is called by FunctionC which is
called by ProcD.
If I change ViewA then I must recreate FunctionB, FunctionC and ProcD
in the correct order.

This can be a real pain trying to keep track of all the dependencies
etc.

If I don't bother about dependencies then I could just change ViewA -
a lot simpler.

But what is the ramification of not bothering with dependencies?

Any answers or suggestions would be greatly received.

Reply to this Message

Erland Sommarskog - 18 Jul 2008 12:12 GMT

> I have about 1000 views, functions and stored procedures in my
> database. They are encrypted for security reasons - my database is
> used by a number of clients. I have all the scripts held in separate
> files on my PC.

No, they are not encrypted. They are obfusticated. That is, a user
that wants to retrieve the definition can do so. The obfustication
acts as "do not enter", nothing more. It is not secure.

> If I don't bother about dependencies then I could just change ViewA -
> a lot simpler.
>
> But what is the ramification of not bothering with dependencies?

The dependency information is there for your own convenience only. That
is, SQL Server does not rely on them for its purposes.

Maintaining complete dependency information can indeed be difficult.
SQL 2008 ships with a new style of saving dependencies, but it turns
that neither this is complete.

Since you have your code on disk, you may find it quicker to search for
references in the code.

Signature

Erland Sommarskog, SQL Server MVP, esquel@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

Reply to this Message

ambradnum@hotmail.com - 18 Jul 2008 13:47 GMT

> (ambrad...@hotmail.com) writes:
> > I have about 1000 views, functions and stored procedures in my
[quoted text clipped - 5 lines]
> that wants to retrieve the definition can do so. The obfustication
> acts as "do not enter", nothing more. It is not secure.

I agree about the obfustication - I in fact have scripts (gained
through Google Groups) to allow me to retrieve the definition.
My point simply was that Enterprise Manager cannot script these
objects - but thank you for replying here.

> > If I don't bother about dependencies then I could just change ViewA -
> > a lot simpler.
[quoted text clipped - 16 lines]
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you very much for your reply - we do actually find it easier to
search the code on disk.

Reply to this Message

Bob - 18 Jul 2008 13:46 GMT

Can't you do this with sp_refreshview and sp_refreshsqlmodule?

-- Wrap these in a cursor for example
-- Run sp_refreshsqlmodule for ( stored procedure, scalar function,
table-valued-function, inline table-valued function ), SQL only
SELECT 'EXEC sp_refreshsqlmodule ' + QUOTENAME( SCHEMA_NAME ( schema_id ) +
'.' + name ) AS refresh_sql
FROM sys.objects
WHERE type In ( 'P', 'FN', 'TF', 'IF' )

SELECT 'EXEC sp_refreshview ' + QUOTENAME( SCHEMA_NAME ( schema_id ) + '.' +
name ) AS refresh_sql
FROM sys.objects
WHERE type = 'V'
AND OBJECTPROPERTY( object_id, 'IsSchemaBound' ) = 0

-- Demo

USE tempdb
GO

DROP PROC usp_test1
DROP PROC usp_test2
GO

CREATE PROC usp_test2

AS

EXEC usp_test1
GO

-- Should get warning; check dependencies
EXEC sp_depends 'usp_test2'
GO

CREATE PROC usp_test1

AS

SELECT 1 x
GO

-- Should still be empty
EXEC sp_depends 'usp_test2'
GO

-- Refresh now
EXEC sp_refreshsqlmodule usp_test2
GO

-- Should get results
EXEC sp_depends 'usp_test2'
GO

Reply to this Message

Erland Sommarskog - 18 Jul 2008 23:07 GMT

> Can't you do this with sp_refreshview and sp_refreshsqlmodule?

No, he can't. As he is using EM, I assume he is on SQL 2000, and
sp_refreshsqlmodule was added in SP2 of SQL 2005.

But not sp_refreshsqlmodule gives you all. Consider a procedure that
creates a temp table and has a query which joins regular tables with
the temp table. If there are no other references to those tables,
they will not be entered in the dependencies. Unless, that is, you
first create the temp table.

Signature

Reply to this Message

Roy Harvey (SQL Server MVP) - 19 Jul 2008 00:56 GMT

Assuming you are using SQL Server 2000, there is a brute force way
that, as far as I know, gets the dependencies as up to date as they
can get. Make a script file of all the objects EXCEPT tables. Make
sure it has NO drop commands, and change all the CREATE commands to
ALTER. Now run the script as many times as there are levels of
nesting of object references. (That many runs might be overkill, but
it used to be required in SQL Server 7.0 and it can't hurt anything.)

Clumsy at best.

Roy Harvey
Beacon Falls, CT

>I have about 1000 views, functions and stored procedures in my
>database. They are encrypted for security reasons - my database is
[quoted text clipped - 25 lines]
>
>Any answers or suggestions would be greatly received.

Reply to this Message

Erland Sommarskog - 19 Jul 2008 09:22 GMT

> Assuming you are using SQL Server 2000, there is a brute force way
> that, as far as I know, gets the dependencies as up to date as they
[quoted text clipped - 3 lines]
> nesting of object references. (That many runs might be overkill, but
> it used to be required in SQL Server 7.0 and it can't hurt anything.)

I don't think this works. In SQL 2000 when you run ALTER PROCEDURE, all
references in sysdepends to that procedure are deleted. If you have an
SQL 2000 instance at hand you can try this:

CREATE PROCEDURE inner_sp AS PRINT 'Dummy'
go
CREATE PROCEDURE outer_sp AS EXEC inner_sp
go
sp_depends inner_sp -- lists outer_sp
go
ALTER PROCEDURE inner_sp AS PRINT 'Yummy'
go
sp_depends inner_sp -- lists no references
go
DROP PROCEDURE inner_sp, outer_sp

This is fixed in SQL 2005.

Signature

Reply to this Message

Roy Harvey (SQL Server MVP) - 19 Jul 2008 14:13 GMT

>I don't think this works. In SQL 2000 when you run ALTER PROCEDURE, all
>references in sysdepends to that procedure are deleted. If you have an
[quoted text clipped - 13 lines]
>
>This is fixed in SQL 2005.

Learn something every day. Thanks Erland!

Roy Harvey
Beacon Falls, CT

Reply to this Message