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.