Thursday, April 29, 2021

Where did my table valued functions go in Azure Synapse?

I recently discovered that you can create table functions in Azure Synapse,  but they are NOT listed in SSMS.  Also, even if you know the function's name, you can't use sp_helptext to find out what it does.

So how can we find them or find out their definitions if we know someone created some functions? 

Here's the query you need to pull from system tables:


SELECT s.name
    ,o.name
    ,DEFINITION
    ,type
FROM sys.sql_modules AS m
JOIN sys.objects AS o
   ON m.object_id = o.object_id
JOIN sys.schemas AS s
   ON o.schema_id = s.schema_id
WHERE o.type IN ('IF','FN');
GO

Have fun with Azure Synapse!


Deep Dives, Best Practices, and What's Coming in Microsoft Fabric - FabCon 2026 - Part 2

  FabCon 2026 — Recap Part 2 Deep Dives, Best Practices, and What's Coming in Microsoft Fabric My notes from Fabcon sessions covering De...