SQL User Defined Table Types: Why can we drop them if not used as parameter?

Question

We all know that SQL User Defined Table Value Table Types (UDT) cannot be dropped if they have dependents/dependencies. Right.

But, today I dropped one even if they have dependents. Only criteria is they should not be used as parameters of DB objects like proc or func.

CREATE TYPE FooUDT AS TABLE
(
  ID int NOT NULL
)

Dependent

CREATE PROCEDURE Bar
as
BEGIN
    DECLARE @Identifier FooUDT

    --Some operations on @Identifier
END
GO

The FooUDT can be dropped as it is used inside a proc and is not a parameter. But following way it cannot be dropped.

CREATE PROCEDURE Bar 
    @Identifier FooUDT readonly
as
BEGIN
    --Some operations on @Identifier
END
GO

What's more interesting is that in both cases, if we check the dependencies, both will show each other name. Yet the former case can be dropped but not the latter. Why this? Or am I missing something?

Answer 1

SQL Server stores the Stored Procedure body as text which your DECLARE @Identifier FooUDT is in the body of the procedure.

Select text, *
    from sysobjects A
    JOIN syscomments B
    On A.id = B.id        
    where xtype = 'P'

Parameters are stored in metadata however. You can review them as follows...

SELECT SCHEMA_NAME(SCHEMA_ID) AS[Schema],
SO.name AS[ObjectName],
SO.Type_Desc AS[ObjectType(UDF / SP)],
P.parameter_id AS[ParameterID],
P.name AS[ParameterName],
TYPE_NAME(P.user_type_id) AS[ParameterDataType],
P.max_length AS[ParameterMaxBytes],
P.is_output AS[IsOutPutParameter]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P
ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.OBJECT_ID IN(SELECT OBJECT_ID FROM sys.objects WHERE TYPE IN('P', 'FN'))
ORDER BY[Schema], SO.name, P.parameter_id

I will let others chime in here, but I believe you would run into quite a few update anomalies and cascade issues if you tried to check procedure bodies for dependencies.

Answer 2

Michael buller's answer is spot on

Only parameters are validated on drop, stored procs or UDFs bodies are not; a simple example of why it wouldn't be practical to validate the bodies, is the existence of dynamic SQL through sp_execute_sql

https://msdn.microsoft.com/en-us/library/ms188001.aspx

Another reason would be the need to recompile all SPs/UDFs on every schema change. Instead, they are only recompiled on demand, particularly when created or altered.

You see the dependencies because they were calculated at compilation time; but since changes may have ocurred, dependencies are not necesarily up to date, and DROP should work for code that is no longer valid...