How to find a UDT?

• Schadenfreude-Mei

  SSChampion

  Points: 14083

  More actions

  March 27, 2009 at 5:38 am

  #155475

  Hey all,

  We have a database which used to use UDT's (now removed). It turns out that there are still some left (but we dont know on to which table).

  DROP TYPE fails with the 'currently in use' message but doesnt show dependancy.

  I have the UDT names, now i just need to know how to search for them (over 300 tables).

  ANyone have any ideas?

  Adam Zacks-------------------------------------------Be Nice, Or Leave

• Schadenfreude-Mei

  SSChampion

  Points: 14083

  More actions

  March 27, 2009 at 5:45 am

  #967323

  Sorted! Thanks anyway.

  select domain_name,table_catalog,table_name,column_name

  from {database}.information_schema.columns

  where left(domain_name,3)='udt'

  or left(domain_name,4)='zstd'

  Adam Zacks-------------------------------------------Be Nice, Or Leave

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737403

  More actions

  March 27, 2009 at 9:02 am

  #967507

  Thanks for the update. Glad you found it.