11

I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.

Checking one single stored procedure is easy (I just rerun the alter script and see whether the operation is successful), but doing that on 100+ procedures is a bit cumbersome.

I know I can use a script like this one to recompile all the objects of my database, but the actual operation will take place next time the stored procedure is executed, not immediately, so that doesn't seem appropriate in my case.

I was also thinking that I could drop all the stored procedures altogether, and resycnhronize my database with my source control system, but that option, although viable, isn't very elegant. Is there a better way of doing this?

I'm using SQLServer 2008 R2 and my database scripts are stored in a VS 2008 database project.

To clarify, I'm not advocating one should solely rely on this approach to test code. Just exactly like in c# you instantaneously detect syntax error in other dependent files as you code (and then use other strategies to test such as unit tests, which is usually several orders of magnitude slower), I think it would make sense to detect SQL dependencies errors in seconds rather than having to run a full functional test which can typically take a few hours to complete.

Improve this question

6 Answers 6

Reset to default
6

How about you run your your unit, functional, integration and performance tests? If you don't have any tests then is serious time to start considering your database schema as code and treat it as such, including version control and testing. Alex Kuznetsov has an entire book dedicated to this subject: Defensive Database Programming with SQL Server.

Improve this answer
5
  • Tests don't always cover 100% of the code, and when they do they typically take a few hours to run. In c#, I can detect whether my code still compiles in seconds (regardless of it's correctness). This doesn't mean I should push code (regardless of the code being c# or PLSQL) into production without properly testing it, but it doesn't seem unreasonable to have a way to quickly detect broken dependencies, does it?
    – Brann
    Apr 10, 2012 at 8:18
  • 2
    Unfortunately the SQL Server state of the art right now vis-a-vis dependency detection in stored procedure is 'deeply broken', see Understanding SQL Dependencies or Keeping sysdepends up to date in SQL Server 2008. There are even 3rd-party tools
    – Remus Rusanu
    Apr 10, 2012 at 17:04
  • 2
    This makes unit/functional tests pretty much the only reliable way of detecting breaking changes.
    – Remus Rusanu
    Apr 10, 2012 at 17:04
  • 1
    For a quick check the Visual Studio Database Projects does a pretty decent job at validating any change.
    – Remus Rusanu
    Apr 10, 2012 at 17:06
  • Ah, the canonical StackOverflow answer.... [Q: "How do I do X"] ... [A: "Don't do X, do Y, which involves restructuring your entire project."]
    – Pxtl
    Apr 28, 2021 at 18:02
4

It's a work around, but you could generate the CREATE PROCEDURE scripts for the database (right click database -> tasks -> generate scripts), find and replace CREATE PROCEDURE with ALTER PROCEDURE and then parse.

I hope you get a better answer here - I'm interested too! :)

Improve this answer
3
  • I'm not marking your answer as accepted because I still hope for a cleaner solution (hopefully a scriptable one), but you definitely get my +1! Thanks.
    – Brann
    Apr 9, 2012 at 16:10
  • 3
    This approach won't let you know if you're referencing a non-existent table.
    – Nick Chammas
    Apr 9, 2012 at 20:58
  • This approach also won't work if the generated script is larger than around 30k lines. I hate that I know this..
    – Eonasdan
    Jan 27, 2017 at 20:43
4

You can use Sql Server Data Tools (SSDT). Microsoft Visual Studio allows you to create a Sql Server project. One then imports the database into the project and then build the project. If there are any broken stored procedures or objects, you will get a compile error.

Improve this answer
1
  • I'll add that you can easily generate a new database create script from the SSDT project and run in a test environment, which will be a pretty thorough verification that there's no procs/triggers/etc broken due to schema changes.
    – AaronLS
    Aug 27, 2018 at 21:06
3

You may want to look at this SO question I'm looking for a reliable way to verify T-SQL stored procedures. Anybody got one? which is asking essentially the same thing, with several answers.

To build upon the script Alaa Awad posted... this should show the schema and database of the referenced and referencing objects. If you're using many temp tables via aliases (which sometimes show up when using sys.sql_expression_dependencies), UDTT parameters or other dynamic features you may need to use the functions sys.dm_sql_referenced_entities or sys.dm_sql_referencing_entities instead/also.

SELECT
    DB_NAME() + '.' + OBJECT_SCHEMA_NAME(sed.referencing_id) + '.' + OBJECT_NAME(sed.referencing_id) AS [referencingObject],
    isnull(sed.referenced_server_name + '.', '') + isnull(sed.referenced_database_name + '.', DB_NAME() + '.') + isnull(sed.referenced_schema_name + '.', OBJECT_SCHEMA_NAME(sed.referencing_id) + '.') + sed.referenced_entity_name AS [missingReference]
FROM 
    sys.sql_expression_dependencies sed
WHERE 
    sed.is_ambiguous = 0
    AND OBJECT_ID(isnull(sed.referenced_database_name + '.', DB_NAME() + '.') + isnull(sed.referenced_schema_name + '.', OBJECT_SCHEMA_NAME(sed.referencing_id) + '.') + sed.referenced_entity_name) IS NULL
ORDER BY
    [referencingObject], [missingReference]
Improve this answer
1
  • 1
    Should add these to the where clause: /* Not an Existing UserType / AND sed.referenced_entity_name NOT IN (SELECT [name] FROM sys.types) / Not an alias */ AND sed.referenced_schema_name IS NOT NULL
    – JasonBluefire
    Jan 25, 2019 at 21:22
1

use the sys.sql_expression_dependencies added in sql server 2008

CREATE PROCEDURE [dbo].[spMaintenance_Find_Broken_Dependencies]

AS
SELECT
    OBJECT_NAME(referencing_id) AS [referencingObject],
    referenced_entity_name AS [missingReference]
FROM 
    sys.sql_expression_dependencies
WHERE 
    is_ambiguous = 0
    AND OBJECT_ID(referenced_entity_name) IS NULL
ORDER BY 
    OBJECT_NAME(referencing_id), referenced_entity_name

GO
Improve this answer
1
  • This may be useful, however it's not so simple as the schema's also need taking into account. I'm also getting issues where the sys.sql_expession_dependencies is displaying the alias used rather than the actual dependent table, which obviously fails the object_id() test. Finally it brings up user-defined tables passed as parameters to stored procedures - which isn't really useful.
    – Tabloo Quijico
    Oct 28, 2014 at 11:34
0

There is one more way by creating a dacpac and then importing it in SQL server. You can then build the solution and it will show you all the errors that It will encounter with the whole database. Please note that you need to create DACPAC for all the linked databases.

Hope it help.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.