4

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.

3

This will give you a list of constraints and their definitions which contain NEXT VALUE FOR, which implies they are a sequence. Then you can see the sequence name in the definition.

SELECT OBJECT_NAME(c.object_id) TABLE_NAME,
       c.name COLUMN_NAME,
       dc.name CONSTRAINT_NAME,
       dc.definition [DEFINITION]
FROM sys.columns c
    INNER JOIN sys.default_constraints dc
        ON c.default_object_id = dc.object_id
WHERE dc.definition LIKE '%NEXT VALUE FOR%'

You could reverse this to search by sequence name.

However, if you do not have access to sys.sequences, it's unlikely you'll have any access to tables above.

I'd suggest getting an admin to put the refined code into a stored proc that has permissions, so you can access it without getting access to the underlying tables.

I can't seem to find a direct link between a sequence and a constraint or column, the definition is the only mention of it I can find. I suppose it's not a traditional relationship, just a block of code the constraint runs.

  • Thank you Ollie - that worked. Interestingly, the sequences that I was after were not actually used in any tables. I do have access to the tables BTW, just didn;t have access to look under the hood of sys.sequences to see if there were any FK relationships e.g. to sys.columns to give me a clue – nonpoliticaltag Aug 2 '17 at 11:24
  • You may want to also look at sys.sql_modules as it could be referenced inside a stored procredure. But it could be utilized in an ad-hoc piece of code, or through an SSIS package, or lots of places. – Jonathan Fite Sep 12 '18 at 13:24
6

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
1

I had the same question and found the solution provided by @Geoff-Patterson. I found the solution a bit slow and filtering it was almost as slow as running the entire statement.

This statement should give the same result, but in a much faster way.

SELECT 
s.object_id AS sequence_object_id,
s.name AS sequence_name,
SCHEMA_NAME(oParent.schema_id) +'.'+ oParent.name AS table_name,
SCHEMA_NAME(o.schema_id) AS referencing_schema_name,
o.name AS referencing_entity_name,
dep.referencing_id,
dep.referencing_class,
dep.referencing_class_desc,
dep.is_caller_dependent

FROM sys.objects AS o
INNER JOIN sys.sql_expression_dependencies AS dep on dep.referencing_id = o.object_id
INNER JOIN sys.sequences AS s ON dep.referenced_id = s.object_id
INNER JOIN sys.objects AS oParent ON o.parent_object_id = oParent.object_id
--WHERE oParent.name = 'TableName'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.