How to get Columns Along with ForeignKey Columns

Question

Hi

Suppose I have 2 table with Promary Key- ForeignKey Relatiosships

Order ->OrderDetails

In DataContext we take object named Dc

then we do dc.Orders

in it we also got column named OrderDetails TOGETHER WITH OTHER COLUMNS

In SMo Table Object

If we do Table.Columns then we only got columns 

we dont get foreign key columns together with column list

I want Columns & ForeignKey Column Together

Like 

In Order Table we get

Id

Name 

Date

OrderDetails  -> ForeignKey Like Object 

I want to get this kind of columns list(Columns with ForeignKeyColumn) from table object

Can you please help me for it?

Answer 1

Is this what you mean?

Column.EnumForeignKeys Method

"Enumerates a list of foreign keys that reference the column."

---

Uros Calakovic

Answer 2

yes same But What I Want is

In List I want to show normal columns as well as ForeignKeyColumn together

Like Suppose tables Order -> OrderDetails Relations

then in OrderDetails Column list I want to show following normal as well as foreignkey column together

Like

Id  <- normal column

Detail name <- normal column

Detail description <- normal column

Date <- normal column

Orders <- ForeignKeyColumn

Here If user select Orders then in second List Columns of Order table should be fill along with normal & foreignKey Columns

I want that kind of thing

Can you please help me for it?

Answer 3

I think following t-sql works for u : 

You have to pass only Table Name.

select co.name as Column_Name,case when ccu.CONSTRAINT_NAME IS NUll then 'Normal Column' else ccu.CONSTRAINT_NAME end as Column_Type
from sys.tables ta
inner join sys.columns co on ta.object_id = co.object_id
left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on ta.name = ccu.TABLE_NAME and co.name = ccu.COLUMN_NAME
where ta.name = 'YourTableName'

Regards,

Bhushan Shah

Answer 4

It is very useful.

Answer 5

Following blog on listing PK-s & FK-s in the database:

http://www.sqlusa.com/bestpractices2005/primaryandforeignkeys/

Flagging PK-s, FK-s and Identity columns:

http://www.sqlusa.com/bestpractices2008/identity-pk-fk-columns/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012