snippetsqlMinor

How to determine which tables use a sequence in SQL Server

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

tablesserversqlsequencedeterminehowwhichuse

Problem

I have a sequence in a SQL Server 2012 database that appears in sys.sequences. How do I determine which tables use this sequence? I do not have sufficient permissions to view the definition of this system view so can't dig around for clues.

Solution

A quick test shows that sequence references are tracked via sys.dm_sql_referencing_entities in SQL Server 2014 SP2. I don't have access to SQL Server 2012, but you can use the following query to (have an admin) confirm whether they're tracked in 2012 as well. If so, this might provide a slightly more rigorous way to find the references you are looking for.

-- Find all objects that reference each sequence on the database
SELECT s.object_id AS sequence_object_id,
    s.name AS sequence_name,
    OBJECT_SCHEMA_NAME(o.parent_object_id) + '.'
        + OBJECT_NAME(o.parent_object_id) AS table_name,
    r.*
FROM sys.sequences s
CROSS APPLY sys.dm_sql_referencing_entities(
    OBJECT_SCHEMA_NAME(s.object_id) + '.' + s.name,
    'OBJECT'
) r
JOIN sys.objects o
    ON o.object_id = r.referencing_id

Code Snippets

-- Find all objects that reference each sequence on the database
SELECT s.object_id AS sequence_object_id,
    s.name AS sequence_name,
    OBJECT_SCHEMA_NAME(o.parent_object_id) + '.'
        + OBJECT_NAME(o.parent_object_id) AS table_name,
    r.*
FROM sys.sequences s
CROSS APPLY sys.dm_sql_referencing_entities(
    OBJECT_SCHEMA_NAME(s.object_id) + '.' + s.name,
    'OBJECT'
) r
JOIN sys.objects o
    ON o.object_id = r.referencing_id

Context

StackExchange Database Administrators Q#182463, answer score: 7

Revisions (0)

No revisions yet.