Find all fields - 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

General SQL Server Forums

New to SQL Server Programming

Find all fields

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-17 : 16:09:12

How would i go about finding all tables in a db which have a specific name? Furthermore, then specifying a common value between that field in those tables.

i.e., currently: SELECT * FROM dbo.AccidentNarrative WHERE id='1'

First, i want to know how many of the 200+ tables in the db, have a column named "ID", and then i want to query each of those tables for a value of '1'.

All help is appreciated.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-17 : 16:14:01

oops. sorry, i meant to say in the first sentence: "find all tables in the db which have a COLUMN with a specific name"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-17 : 16:19:52

Hello WJHamel,

Which version of SQL Server are you using?

TIA.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-17 : 16:20:32

2008R2

whatamouth
Starting Member

16 Posts

Posted - 2012-01-17 : 16:21:03

select t.name [TABLE NAME] from sys.tables t inner join sys.all_columns c on c.object_id=t.object_id
where c.name = 'ID'

Neil Matias
neilmatias@yahoo.com

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-17 : 16:26:15

quote:
Originally posted by WJHamel

2008R2

Thanks for your reply,

You could use a query similar to whatamouth proposed to find all the table with a specific column or you could use the information_schema. Something like;

SELECT TABLE_NAME
FROM information_schema.COLUMNS
WHERE COLUMN_NAME = 'ID'

But (unless I am mistaken) to query each table for specific value(s) would require dynamic SQL or some other tier to perform it programmatically.

HTH and Best wishes.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-17 : 16:32:28

Occem's razor: ehorn wins.

thanks all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources