Identify procs referencing user-defined table type

Question

Post reply

Identify procs referencing user-defined table type

Xedni

SSCertifiable

Points: 6951
More actions
February 12, 2016 at 10:25 am

#324322

Hi SSC,
Is there a deterministic way to tell all the procedures which reference a certain table type? Basically, since you can't alter table types or even drop them while referenced, I have to do some sneaky rename logic, and use sp_refreshsqlmodule on the procs.
I can get close with simple pattern matching, and looking at sys.parameters, but that won't deterministically catch all references INSIDE procs. Clearly under the covers SQL is doing that check, or it wouldn't yell at me, but do you know of a sql statement I can run to do that?
Executive Junior Cowboy Developer, Esq.[/url]
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

The most reliable way I have found is to use the free tool Redgate SQL Search[/url]

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Xedni SSCertifiable Points: 6951 More actions · Answer 2

But no way to do it in SQL? I'm trying to avoid having to have a real live human have to look it all up ahead of time.

Executive Junior Cowboy Developer, Esq.[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

You could use a wild card search on the definition column of sys.sql_module.

Xedni SSCertifiable Points: 6951 More actions · Answer 4

Yeah, I actually mentioned that approach, but it's not always possible to get JUST those with those procs. If you had a TT called MyTableOfDoom and a presentation table called MyTableOfDoomAndTacos, the latter would match as well. I guess it wouldn't be the end of the world to refresh unnecessary procs, but I'd rather know exactly.

Executive Junior Cowboy Developer, Esq.[/url]

ScottPletcher SSC Guru Points: 101089 More actions · Answer 5

I would think sys.sql_expression_dependencies would contain those references, since a table-type is in sys.objects, type = 'tt'.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

ScottPletcher (2/12/2016)
I would think sys.sql_expression_dependencies would contain those references, since a table-type is in sys.objects, type = 'tt'.

Only way to know for sure is test it out. I may do that later if I have time.

Xedni SSCertifiable Points: 6951 More actions · Answer 7

Huzzah! That did it. Thanks, Scott!

Executive Junior Cowboy Developer, Esq.[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Having that issue right now, the problem comes not when the type is in the code, but when it's a parameter. An easy way to find problems would be like this:

SELECT pro.name

FROM sys.parameters par

JOIN sys.procedures pro ON par.OBJECT_ID = pro.object_id

JOIN sys.types typ ON par.user_type_id = typ.user_type_id

WHERE typ.name = 'TableTypeName'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Xedni SSCertifiable Points: 6951 More actions · Answer 9

Thanks, Luis. sys.parameters is great for solving some weird problem like that.

Executive Junior Cowboy Developer, Esq.[/url]