Table Analysis Made Easy

Run this query in your database to get a list of all tables, comma delimited lists of what they reference, comma delimited list of what references them, row count, and total number of stored procedures and function which reference them.

declare @t table (

dbname sysname not null,

tablename sysname not null,

records int not null,

childtables varchar(max) null,

parenttables varchar(max) null,

refcount int null)

declare @tablename sysname

declare @refcount int

insert into @t (dbname, tablename, records, childtables, parenttables)

select DB_NAME(), t.name, i.rowcnt,

STUFF(( SELECT ',' + c.name

      from sys.foreign_keys fk

      inner join sys.objects c on fk.parent_object_id = c.object_id

      where t.object_id = fk.referenced_object_id

      FOR XML PATH('')),1 ,1, '') as children,

STUFF(( SELECT ',' + c.name

      from sys.foreign_keys fk

      inner join sys.objects c on fk.referenced_object_id = c.object_id

      where t.object_id = fk.parent_object_id

      FOR XML PATH('')),1 ,1, '') as parents

from sys.objects t

inner join sysindexes i on t.object_id = i.id

where t.type = 'u'

and t.name <> 'dtproperties'

and t.name not like 'mspeer%'

and t.name not like 'mspub%'

and t.name not like 'sys%'

and i.indid in (0, 1)

order by t.name