SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Mining the sys* tables for information
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LaurieCox
Posting Yak Master

USA
149 Posts

Posted - 06/25/2014 :  16:24:39  Show Profile  Reply with Quote
We have table that is in two databases that is supposed to be the same but got out of whack (i.e. the same Primary Key in the tables do not reference the same thing). So we have to fix this but the problem is that the table's primary key is a foreign key in an unknown number of tables.

There are lots of tables in the database (some we use and some we don't use) and I need to find the tables that we use and that have a relationship with the table (Plans) in question.

So after some googling I came up with this query:

SELECT t.name AS table_name
     , i.rows
     , COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName
     , OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName
     , COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.tables AS t
INNER JOIN sys.sysindexes i on (t.object_id = i.id and i.indid < 2)
INNER JOIN sys.foreign_keys f on f.parent_object_id = t.object_id
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

WHERE 1 = 1
  and OBJECT_NAME (f.referenced_object_id) = 'Plans'
  and rows > 0
ORDER BY table_name;

…which gave me seven tables. My question: Is this query correct and can I trust it?

Thanks,

Laurie

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1721 Posts

Posted - 06/25/2014 :  19:08:26  Show Profile  Reply with Quote
Your logic seems right. Here is a script that we use to perform a similar activity. Given a table name, find all of the tables that reference it by a foreign key:
select
	fk.Name			fkName,

	parent.Name		tblName,
	fkcc.Name		colName,
	fkc.constraint_column_id,

	reference.Name	refTable,
	refCol.Name		refColumn,

	case delete_referential_action
		when 0 then N'NO ACTION'
		when 1 then N'CASCADE'
		when 2 then N'SET NULL'
		when 3 then N'SET DEFAULT'
		else N'<Unknown>'
		end,

	case update_referential_action
		when 0 then N'NO ACTION'
		when 1 then N'CASCADE'
		when 2 then N'SET NULL'
		when 3 then N'SET DEFAULT'
		else N'<Unknown>'
		end,

	fk.is_not_for_replication,

	fk.is_disabled
from
	sys.foreign_keys fk
inner join
	sys.foreign_key_columns fkc
		on	fkc.constraint_object_id = fk.object_id
inner join
	sys.objects parent
		on	fk.parent_object_id = parent.object_id
inner join
	sys.columns fkcc
		on	fkcc.object_id = parent.object_id
		and	fkcc.column_id = fkc.parent_column_id

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

inner join
	sys.columns refCol
		on	refCol.object_id = reference.object_id
		and	refCol.column_id = fkc.referenced_column_id
where
	reference.Name = 'Plans'
order by 
	parent.Name,
	fk.Name,
	fkc.constraint_column_id
HTH but if not, just throw it away.



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

LaurieCox
Posting Yak Master

USA
149 Posts

Posted - 06/26/2014 :  08:20:29  Show Profile  Reply with Quote
Hi Bustaz Kool,

Thanks for the reply. Your code confirms that my code works. When I took out the rows > 0 condition from my query I got the same set of tables that your query pulls.

Thanks,

Laurie
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1721 Posts

Posted - 06/27/2014 :  17:31:40  Show Profile  Reply with Quote
Life is good, again...



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000