User-defined table types: why can we discard them if they are not used as a parameter?

Question

We all know that user-defined (UDT) SQL table value table types cannot be deleted if they have dependencies / dependencies. Correctly.

But today I left him, even if they have dependents. Only criteria - they should not be used as parameters of database objects, such as proc or func.

CREATE TYPE FooUDT AS TABLE ( ID int NOT NULL ) 

According to

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

FooUDT can be dropped because it is used internally by proc and is not a parameter. But in the following way it cannot be discarded.

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

What is even more interesting is that in both cases, if we check the dependencies, both will show each other. But the first case can be discarded, but not the last. Why is this? Or am I missing something?

Answer 1

SQL Server saves the body of the stored procedure as text that 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 saved in metadata. You can view 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 call back here, but I believe that you will encounter fairly small update anomalies and a cascade if you try to check the procedure bodies for dependencies.

Answer 2

Michael Blecker answers

Only parameters in quotation marks, stored procedures, or UDFs bodies are checked; a simple example of why it would not be practical to check bodies is the existence of dynamic SQL via sp_execute_sql

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

Another reason is the need to recompile all SP / UDFs every time the schema changes. Instead, they are only recompiled on demand, especially when they are created or modified.

You see the dependencies because they were calculated at compile time; but since changes may occur, dependencies are not necessarily updated, and DROP should work for code that is no longer valid ...