Why Procedure Estates Become Unknowable
Individually, a stored procedure is just SQL. Collectively, they form deep call graphs: procedures call procedures that call procedures, and the effective behavior of a top-level call is the union of everything beneath it. Names stop being reliable guides, a routine that looks like a validation check may post transactions two calls down.
The knowledge of which procedures are safe concentrates in a few senior people, and every change request pays an archaeology tax while someone re-derives what the code actually touches.
Effect Classification: Turning the Estate into Data
The scalable answer is automated analysis. Parse every procedure body for its reads, writes, calls, and parameters; resolve the call graph; and propagate effects transitively so a procedure inherits the writes of everything it calls. SyteRay runs exactly this crawl against your database.
On its reference deployment, the 8,636-procedure catalog breaks down into 1,951 read-only, 2,437 writing persistent data, 3,343 writing only temp tables, and 819 using dynamic SQL, with 799 procedures escalated to write-classified purely through transitive analysis. A stratified manual audit of 100 procedures found zero misclassifications.
- Every procedure parsed for reads, writes, calls, and parameters.
- Effects propagate through nested calls, catching wrappers that delegate their writes.
- Temp-table-only writes are distinguished from persistent writes, a critical SyteLine nuance.
- The classification gates execution: write-effect procedures require explicit confirmation.
What You Do with a Classified Catalog
Impact analysis becomes a query: which procedures write this table, directly or transitively? Upgrade planning starts from an inventory of what exists and what calls it. Dead-code review starts from procedures nothing calls.
And AI systems executing procedures gain a safety model: read-only procedures can run freely while anything with write effects, even inherited ones, is gated behind human confirmation, which is how SyteRay treats them.
Try it here, free
SyteLine SQL Server Core Licensing Calculator
Counts the core licences and 2-core packs a per-VM SQL Server licensing model needs for SyteLine, then applies your price and Software Assurance rate over several years.
Frequently asked questions
How many stored procedures does a typical SyteLine database have?
Thousands. The delivered product ships a large procedure set, and customizations add more over time. SyteRay's reference deployment catalogs 8,636 procedures; larger or older installations can exceed that.
Why is changing a SyteLine stored procedure risky?
Because effects are transitive. A procedure you change may be called by dozens of others, and the procedures it calls may write tables you did not expect. Without a resolved call graph, every change carries unknown blast radius, which is why effect classification and impact analysis matter.
Can AI safely execute SyteLine stored procedures?
Only with an effect model. SyteRay classifies every procedure's full transitive effects first, then applies a safety ladder: read-only procedures are treated like queries, while anything with direct or inherited write effects requires explicit, previewed confirmation before execution.
See your own stored-procedure estate classified. A SyteRay evaluation includes the full crawl.