Validate T-SQL programmability objects

Is there a way to validate programmability objects in SQL Server 2008?

I have a database with ~500 programmability objects which depend on other programmability objects (not only tables).

If I do some refactoring, it is very hard find other objects which are broken by the changes. For example if I change the parameter count...

Original state of database:

CREATE FUNCTION [dbo].[GetSomeText]() RETURNS nvarchar(max) AS BEGIN RETURN 'asdf' END

/* uses "GetSomeText()" function */
CREATE FUNCTION [dbo].[GetOtherText]() RETURNS nvarchar(max) AS BEGIN RETURN [dbo].[GetSomeText]() + '-qwer' END

Now I do some refactoring (add pa开发者_JS百科rameter @Num to GetSomeText() function):

ALTER FUNCTION [dbo].[GetSomeText](@Num int) RETURNS nvarchar(max) AS BEGIN RETURN 'asdf' + CAST(@Num as nvarchar(max)) END

Now the function GetOtherText() is broken, because it is calling GetSomeText() function without a required parameter.

Is there a way to get information about this error?

Currently I script every programmability object as ALTER, run the alter script, and check for errors. This way looks to be too complex (and is hard to use in T-SQL only enviroment).

EDIT:

Thanks for answers! I know how to get dependenices or list of all objects. The problem is in checking the body of object. If I get the dependency, is there other way to check validity than run ALTER script?

I don't think there's a way to find the dependency. You can, however, find everything that references the name of the object you're changing like this:

 select OBJECT_DEFINITION(o.object_id) as objectDefinition, *
   from sys.objects o
  where o.type in ('P', 'FN')
    and OBJECT_DEFINITION(o.object_id) like '%GetSomeText%'

o.type in ('P', 'FN') limits the search to P - Procedures and FN - Scalar Functions. Check out more info about OBJECT_DEFINITION: http://msdn.microsoft.com/en-us/library/ms176090.aspx

Perhaps you could try introducing some automated database developer/unit testing.

With 500 SQL objects it would be onerous to go back and 'retro fit' for them all. Best approach might be to incrementally create these tests as the need to refactor/change existing APIs/create new SQL objects arises

These automated tests could then be included as part of your overall continous integration approach. Note for the example given you would still have the issue of finding existing dependencies. But once there was sufficient test coverage the tests should highlight any breaking changes introduced.

I have created a test tool that might be of use - but there are a number of others out there:

http://dbtestunit.wordpress.com/

One of the easiest ways to get dependency is to use sp_depends. This does work with functions, but you need to be sure you are in the right DB context:

USE MyDatabase
EXEC sp_depends @objname = N'dbo.FunctionName' 

This will show you any object whether it be a function, stored proc, table, or view that has a dependency for the listed object.

This is not always accurate with cross-database dependencies, though, so be aware.