Prev: US-MD-Silver Spring: SQL VMWare TV Network DBA
Next: Log Errors BCP
From: Emiel on 1 May 2010 03:04
Check out:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c33aa736-3303-4968-b1fb-8308d935148f



Erland Sommarskog wrote:

Re: Table & Column Name
03-Oct-09

Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes:

Not sure that I understand what you are looking for, but this little
script should give you started:

create table first (a int NOT NULL PRIMARY KEY,
b int NOT NULL,
c int NOT NULL)

create table second (a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
PRIMARY KEY (a, b))

create table third (a int NOT NULL PRIMARY KEY,
b int NOT NULL REFERENCES first(a),
c int NOT NULL,
d int NOT NULL,
CONSTRAINT fk2 FOREIGN KEY (c, d)
REFERENCES second(a, b))
go
SELECT srctbl = o1.name, srccol = c1.name,
targettbl = o2.name, targetcol = c2.name
FROM sys.objects o1
JOIN sys.columns c1 ON o1.object_id = c1.object_id
JOIN sys.foreign_key_columns fkk ON fkk.parent_object_id = o1.object_id
AND fkk.parent_column_id = c1.column_id
JOIN sys.objects o2 ON o2.object_id = fkk.referenced_object_id
JOIN sys.columns c2 ON o2.object_id = c2.object_id
AND fkk.referenced_column_id = c2.column_id
ORDER BY o1.name, o2.name, c1.name, c2.name
go
drop table third, second, first



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Previous Posts In This Thread:

On Saturday, October 03, 2009 4:46 AM
Muhammad Bilal wrote:

Table & Column Name
Hi.

How can i know that a column of a table is in which table as foriegn key &
whats tha name of that foriegn key clun in the other table.


Let if i have a table1 with col1. I want to know that the col1 is in which
table as foreign key column and the name of the that column.


Regards,
Muhammad Bilal

On Saturday, October 03, 2009 9:38 AM
Arif wrote:

RE: Table & Column Name
"Muhammad Bilal" wrote:


try this


select object_name(constraint_object_id) 'constraint name',
object_name(parent_object_id ) 'parent table name',
object_name(referenced_object_id) 'referenced table name'
from sys.foreign_key_columns
where object_name(parent_object_id ) = 'TableName'

On Saturday, October 03, 2009 10:00 AM
Arif wrote:

RE: Table & Column Name
following script will give you the clumn names as well

"Muhammad Bilal" wrote:

On Saturday, October 03, 2009 10:04 AM
Arif wrote:

following script will give you the parent table, referenced table ,
following script will give you the parent table, referenced table ,
constraint name and the clumn names

select object_name(constraint_object_id) 'constraint name',
object_name(parent_object_id ) 'parent table name',
object_name(referenced_object_id) 'referenced table name',
c1.name 'parent column name',
c2.name 'referenced column name'
from sys.foreign_key_columns f
inner join sys.columns c1 on f.parent_column_id = c1.column_id and
c1.object_id = f.parent_object_id
inner join sys.columns c2 on f.referenced_column_id = c2.column_id and
c2.object_id = f.referenced_object_id
where object_name(parent_object_id ) = 'yourtable'



"Muhammad Bilal" wrote:

On Saturday, October 03, 2009 1:50 PM
Erland Sommarskog wrote:

Re: Table & Column Name
Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes:

Not sure that I understand what you are looking for, but this little
script should give you started:

create table first (a int NOT NULL PRIMARY KEY,
b int NOT NULL,
c int NOT NULL)

create table second (a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
PRIMARY KEY (a, b))

create table third (a int NOT NULL PRIMARY KEY,
b int NOT NULL REFERENCES first(a),
c int NOT NULL,
d int NOT NULL,
CONSTRAINT fk2 FOREIGN KEY (c, d)
REFERENCES second(a, b))
go
SELECT srctbl = o1.name, srccol = c1.name,
targettbl = o2.name, targetcol = c2.name
FROM sys.objects o1
JOIN sys.columns c1 ON o1.object_id = c1.object_id
JOIN sys.foreign_key_columns fkk ON fkk.parent_object_id = o1.object_id
AND fkk.parent_column_id = c1.column_id
JOIN sys.objects o2 ON o2.object_id = fkk.referenced_object_id
JOIN sys.columns c2 ON o2.object_id = c2.object_id
AND fkk.referenced_column_id = c2.column_id
ORDER BY o1.name, o2.name, c1.name, c2.name
go
drop table third, second, first



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Submitted via EggHeadCafe - Software Developer Portal of Choice
Excel 2007 Filter Tool
http://www.eggheadcafe.com/tutorials/aspnet/ae703d26-58da-423a-a2cb-1f3a46fbea8f/excel-2007-filter-tool.aspx
 | 
Pages: 1
Prev: US-MD-Silver Spring: SQL VMWare TV Network DBA
Next: Log Errors BCP