Wednesday, October 8, 2014

List procedures and their dependent tables


distinct as 'Procedure Schema Name', as 'Procedure Name', as 'Table Schema Name', as 'Table Name' from sysdepends d

join sys.objects o1 on = o1.object_id

join sys.schemas s1 on o1.schema_id = s1.schema_id

join sys.objects o2 on d.depid = o2.object_id

join sys.schemas s2 on o2.schema_id = s2.schema_id


o1.type = 'P' and

o2.type = 'U'


by 1,2,3,4

Query to look at filegroups by table

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name

, AS table_name


, AS index_name

, AS filegroup_name


FROM sys.tables t

INNER JOIN sys.indexes i ON t.object_id=i.object_id

INNER JOIN sys.filegroups ds ON i.data_space_id=ds.data_space_id

INNER JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
ORDER BY, i.index_id