to view all columns named ename from all tables

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

to view all columns named ename from all tables

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-06-19 : 16:13:05

how do i view all columns from all tables in my databse which have the name 'ename'.

Ashley Rhodes

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-06-19 : 16:17:12

select sc.name, so.name
FROM syscolumns sc, sysobjects so
where sc.name = 'ename'
and so.xtype = 'u' and sc.id=so.id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-19 : 16:18:24

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ename'

Tara Kizer
aka tduggan

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-06-19 : 17:05:44

this query does not gives me the name of the column

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like '%name%'

some times i need to know names of columns as well along with the table name. coz i use the like operator.

THanks for both the replies.

Ashley Rhodes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-19 : 18:35:35

Then just add COLUMN_NAME to the query.

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like '%name%'

The recommendation is to use the INFORMATION_SCHEMA views instead of the system tables, wherver possible that is. Here it is possible.

Tara Kizer
aka tduggan

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-19 : 23:48:55

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like '%name%'

-- KK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-20 : 12:20:44

quote:
Originally posted by CSK

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like '%name%'

-- KK

Isn't that what I posted?

Tara Kizer
aka tduggan

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-06 : 13:10:51

Again I do not have access to information schema. I know I can use it
something like this would not work

select sc.name, so.name
FROM sysconstraints sc, sysobjects so
where
so.type = 'RI' and sc.id=so.id

so I need sysobjects and all

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-06 : 13:13:15

Why don't you have access to the INFORMATION SCHEMA views but you have access to the system objects? That doesn't make sense to me. Could you explain?

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources