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.
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.
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
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'