How to get table name???????? - SQL Server Forums

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)

How to get table name????????

Author

Topic

sweet_777
Starting Member

16 Posts

Posted - 2010-02-01 : 02:09:45

Hi all,

i have one query, i know field name of a table but i know the table name. how can i find out table name using field name.

Thanks & Regards
Sweet_77

Thanks & Regards
Sweet_77

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-01 : 02:13:24

select *
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'something'

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-01 : 02:14:23

[code]
select
o.name as Table,
c.name as Column
from sys.objects o
join sys.columns c on c.object_id = o.object_id and
c.name like '%<searchvalue>%'
order by o.name,c.name
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-01 : 02:15:06

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-01 : 04:10:35

quote:

Originally posted by webfred


select 
o.name as Table,
c.name as Column
from sys.objects o
join sys.columns c on c.object_id = o.object_id and
                      c.name like '%<searchvalue>%'
order by o.name,c.name

No, you're never too old to Yak'n'Roll if you're too young to die.

Dont use system tables. Use Information_schema views

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 04:17:24

quote:
Originally posted by khtan

select c.*
from INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
ON t.TABLE_NAME = c.TABLE_NAME
where c.COLUMN_NAME = 'something'
and t.TABLE_TYPE = 'BASE TABLE'

[blue]KH
[spoiler]Time is always against us[/spoiler]

if you need to avoid views in results

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-01 : 06:05:08

quote:
Originally posted by visakh16

quote:
Originally posted by khtan

select c.*
from INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
ON t.TABLE_NAME = c.TABLE_NAME
where c.COLUMN_NAME = 'something'
and t.TABLE_TYPE = 'BASE TABLE'

[blue]KH
[spoiler]Time is always against us[/spoiler]

if you need to avoid views in results

or

select c.*
from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'something' and
objectproperty(object_id(c.TABLE_NAME),'Istable')=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources