Is it possible to determine by way of T-SQL Query whether a sproc is CLR-based or regular T-SQL-based?

Question

Hey all. I'm integrating a CLR/Assembly-based sproc replacement for an existing sproc that lives within our production database, and I want to write an update script that only drops the old T-SQL-based script and doesn't drop the new one if its already there. Is that possible?

Answer 1

When you query against sys.objects where type = 'P' (or sys.procedures), only SQL stored procedures are returned and not CLR functions. See sys.objects or more.