MSSQL Database Trigger

macf00bar

Hi!

Can LinqPad show Database Triggers in the tree (and possibly give the edit../drop.. right mouse menu for it)?
If yes, how/where/what do I have to change on the connetion properties to see it?
If no, would be a nice to have.

BR, Reinhard

macf00bar

@JoeAlbahari sorry to bother but is this possible with LinqPad - maybe some hidden feature I am unable to find the correct settings for?

JoeAlbahari

LINQPad doesn't show constraints, triggers, indexes or statistics. The Schema Explorer displays only things that can be queried.

macf00bar

@JoeAlbahari said:
LINQPad doesn't show constraints, triggers, indexes or statistics. The Schema Explorer displays only things that can be queried.

What do you mean by "only things that can be queried"?

Couldn't you use something like this (or am I missing the obvious here?)?
MSSQL Triggers:

SELECT name AS TriggerName, OBJECT_DEFINITION(object_id) AS TriggerCode
FROM sys.triggers
WHERE OBJECT_NAME(parent_id) = 'YourTableName'

MSSQL Constraints:
SELECT 
    OBJECT_NAME(c.constid) AS ConstraintName,
    CASE
        WHEN c.status & 0x8000 = 0x8000 THEN 'Primary Key'
        WHEN c.status & 0x2 = 0x2 THEN 'Unique Constraint'
        WHEN c.status & 0x4 = 0x4 THEN 'Foreign Key'
        WHEN c.status & 0x80 = 0x80 THEN 'Default Constraint'
        WHEN c.status & 0x200 = 0x200 THEN 'Check Constraint'
        ELSE 'Unknown'
    END AS ConstraintType,
    OBJECT_NAME(c.id) AS TableName,
    COL_NAME(c.id, c.colid) AS ColumnName,
    OBJECT_DEFINITION(c.constid) AS ConstraintDefinition
FROM sys.sysconstraints c
JOIN sys.objects o ON o.object_id = c.constid
WHERE o.type = 'F' AND OBJECT_NAME(c.id) = 'YourTableName'

JoeAlbahari

Yes, it could be done, but it all has to be coded up in the UI and tested on various versions of SQL Server, SQL Azure, Synapse Classic, Synapse Serverless and Azure Data Explorer. Also, the query to retrieve constraints, triggers and indexes may work correctly but end up running slowly under certain conditions with certain schemas, causing poor performance or timeouts that for some customers prevent LINQPad from querying at all. This all has to be extensively checked and guarded with metering and opt-in/opt-out flags. I'm not saying it can't be done, but it's non-trivial to do it safely.

macf00bar

Thanks for clarification.