List of tables having a specific column as key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

List of tables having a specific column as key

Author

Topic

senthila
Starting Member

1 Post

Posted - 2009-01-08 : 23:32:56

Hi
I am in need of getting a list of all the tables in the database having a specific field as "key" field. For example, I need all the tables in the database that would have "Region" field as KEY field.
I need an SQL query involving metadata tables that would fetch this information. Please help me with this, thanks.
Arul

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-08 : 23:43:46

select table_name,column_name from information_schema.columns where column_name like '%keyfield%'

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-08 : 23:54:05

region field name & if u want to get the primarykey,foreign key used on region field
try this
select distinct i.table_name,i.column_name,s.name from information_schema.columns i
inner join
sys.objects s on s.parent_object_id = object_id(i.table_name)
where i.column_name like '%id%' and s.type in('pk','f')

or if u want region column only
use nageswar query

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-08 : 23:54:42

Are You Looking For Constraint Type Keys or Just columnNames ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources