Typically, T-SQL is not the best platform for programming (understatement). If you have many procedures that call other procedures, that’s a signal that you might be programming in the database.
Find out using this query:
select OBJECT_SCHEMA_NAME(p.object_id) as schemaName, OBJECT_NAME(p.object_id) as procedureName, count(*) as [calls to other procedures] from sys.procedures p cross apply sys.dm_sql_referenced_entities(schema_name(p.schema_id) + '.' + p.name, 'OBJECT') re where re.referenced_entity_name in (select name from sys.procedures) group by p.object_id order by count(*) desc; |
in Adventureworks, we see this result:
To drill down into those results, use this query:
select distinct QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' + QUOTENAME(OBJECT_NAME(p.object_id)) [This procedure...], QUOTENAME(OBJECT_SCHEMA_NAME(p_ref.object_id)) + '.' + QUOTENAME(OBJECT_NAME(p_ref.object_id)) [... calls this procedure] from sys.procedures p cross apply sys.dm_sql_referenced_entities(schema_name(p.schema_id) + '.' + p.name, 'OBJECT') re join sys.procedures p_ref on re.referenced_entity_name = p_ref.name order by 1,2 |
which gives results like this:
Adventureworks seems just fine to me. Only four instances of procedures calling procedures. I looked at the database I work with most. Hundreds of procedures (representing 15% of the procedures) call other procedures. On the other end of the spectrum is Stackoverflow. I understand that they don’t use stored procedures at all.
Good script. Fails when dependencies are on other datatabases. Yes, sadly we have plenty of those cases 🙁
Comment by Yarik — April 20, 2016 @ 3:39 pm
Hi Yarik,
Wow, cross-database procedure dependencies! That is a sad case. Out of curiosity how does the script fail? Do you get an error message? Or do you get less results than you expect?
Comment by Michael J. Swart — April 20, 2016 @ 3:48 pm
Here are sample of the errors. From what I see, is on sp’s that have cross databases references:
The dependencies reported for entity “dbo.xxxxx” might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.
Comment by Yarik — April 20, 2016 @ 3:52 pm
Hmm… I’m not sure how I feel about this. On one hand, I get what you’re saying. On the other though, I like DRY (Don’t Repeat Yourself). If you’ve already committed to writing stored procedures, why duplicate logic in them if you don’t have to? Also, “committed to writing procedures”… database jokes. 😉
Comment by Ben Thul — April 20, 2016 @ 3:56 pm
I know what you’re saying Ben. In the Adventureworks case, I love the how they use a procedure to handle error logging. That’s a perfect example of DRY at work. So I guess I’m not advocating for less of those kind of procedures if it means copy-pasting everywhere.
I guess I’m advocating for keeping things as simple as possible in the database and keeping the complexity in the application or reporting layer. Ideally, procedures should do one logical thing. In the case of the Adventureworks procedures, they do one thing … plus error-handling which is perfectly acceptable. Another signal that things may be getting out of hand: If you ever find yourself stepping the TSQL debugger, or wishing for a TSQL call stack, then that’s programming. And personally I’ve had a lot more success and a lot less trouble when programming is done outside of the database.
Comment by Michael J. Swart — April 20, 2016 @ 4:04 pm
I agree. We are slowly cleaning and reducing inter dependencies, but is a long work as it has impact on the app layer so we need to work together with developers to avoid breaking stuff.
Comment by Yarik — April 20, 2016 @ 4:18 pm
If I may make a shameless plug here, my sp_helpExpandView is built on using those DMVs. And as I discovered, works fine on stored procedures as well!
https://sqlbek.wordpress.com/2015/03/04/sp_helpexpandview-well-i-didnt-expect-that/
Comment by Andy (@SQLBek) — April 20, 2016 @ 4:39 pm
Ok, found what was wrong. I was incorrect, not exactly the sp’s referencing other db’s but more related to this https://technet.microsoft.com/en-us/library/cc879246(v=sql.105).aspx
Comment by Yarik — April 20, 2016 @ 9:14 pm
I have to be honest Yarik, I got the same error when preparing this post (three of them).
I discovered that the error was in the definition of the sproc itself and decided to omit that fact because it might have been distracting to the blog post. 🙂
Thanks for reporting back.
Comment by Michael J. Swart — April 20, 2016 @ 9:20 pm
Same 200 here at DeploymentDB:
Msg 2020, Level 16, State 1, Line 1
The dependencies reported for entity “dbo.RetrievePackages” do not include references to columns…
dbo.RetrievePackages has just one parameter @MacAddress.
on this instance Microsoft SQL Server 2008 (SP3) – 10.0.5861.0 (X64)
The statement “Returns error 2020 when column dependencies cannot be resolved. This error does not prevent the query from returning object-level dependencies” here https://msdn.microsoft.com/en-us/library/bb677185.aspx is true,
select * from sys.dm_sql_referenced_entities(‘dbo.RetrievePackages’, ‘OBJECT’)
returns referenced OBJECTs, but stop the whole query executing.
Comment by IL — April 21, 2016 @ 5:32 am
Hi IL,
As I was saying to Yarik, it sounds like your procedure is referencing an object that is missing. I had the same experience.
But you said that it stops the whole query from executing? I didn’t see that behavior. In SQL Server Management Studio, the query completes and the error is displayed in the messages tab, but the results tab still contains the rest of the results.
Comment by Michael J. Swart — April 21, 2016 @ 8:45 am
There is NOTHING wrong when you call SPs or UDFs from your user-defined SP, as long as you AWARE of such dependencies.
IMHO this is much better outcome than REPEATING huge blocks of T-SQL script.
Should not be a problem if you using DB Unit Test framework like tSQLt
Comment by fregate — April 23, 2016 @ 2:48 am
Hi fregate,
I think we will have to disagree. In my reply to Ben, I mention that I do like the “Don’t Repeat Yourself” principle if it means avoiding repeating huge blocks of SQL. But better still is to avoid any complexity in the database at all.
Comment by Michael J. Swart — April 25, 2016 @ 9:11 am
Visual Studio (yes, a programming tool) is the perfect tool to identify all object dependencies, especially between databases. You create a project and start by importing your database, then hit Build project. If there’s any external dependecy, it will force you to add the database containing that object. And then the same for any external dependency that exists in any of the databases that you add, until the Build project succeeds. And hey presto, you also have your database prepared for Sibversion (or what have you).
Comment by Adriaan Simons — April 24, 2016 @ 3:34 am
Hi Adriaan,
I work for an ISV, and when our product is deployed, it deploys with seven or eight different databases. None of them depend on each-other. Our application depends on each one of them, but there’s no cross-DB dependencies. I consider ourselves lucky.
Comment by Michael J. Swart — April 25, 2016 @ 9:15 am
“Typically, T-SQL is not the best platform for programming (understatement). If you have many procedures that call other procedures, that’s a signal that you might be programming in the database.”
I have to put a huge ‘it all depends’ on that. ASSUMMING that your DB design is good, and your SQL skills are good, and you understand transaction control, there are lots of very good and logical reasons to actually DO in-db programming. You may not want to do lots of it in heavily queried end-used DB’s, but I for years used various ‘workdb’ designs and extensive SQL stored procedures to accomplish massive data operations very quickly and efficiently. Why add front-end code to the mix if you don’t need user interaction? I agree that inter-DB processing probably needs to be limited to data acquisition and distribution, but I see no reason to forgo the advantage of the speed and power of good SQL.
Comment by Rick — April 25, 2016 @ 7:24 am
Hi Rick,
Yes, it depends, and it’s why I always use words like “might” or “maybe” or “typically”.
Where I work, there is always an application and so instead of this pattern:
I prefer this one:
And I see your point. In cases where there is no user interaction (like a maintenance job) it might make sense. In fact if someone looked at my blocked process report viewer, they’d see I’m programming in the database.
But when there’s an application, I like to let the application do it’s job and keep the database about the data. At work, I help support both the application and the database and I see firsthand the problems that occur when we blur the responsibilities of the application with those of the database.
Comment by Michael J. Swart — April 25, 2016 @ 9:01 am
So, one of our databases makes 30 calls to other procs – those sorts are such a joy to tune I tell ya! Thanks for the scripts – they made me sad 😉
Comment by Allen M McGuire — April 28, 2016 @ 2:29 pm