How to find a column in a DB ?

Question

Post reply

How to find a column in a DB ?

SQL SERVER ROOKIE

Ten Centuries

Points: 1032
More actions
November 29, 2012 at 2:21 pm

#271444

Hello,
How to look for a column lets say the column name has the word _commodity_ in it in a SQL DB.
My DB has 100's of tables, and I'm not sure which tables contain columns names stating or ending with the name commodity.
Thanks
SM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

You can query the system tables:

SELECT * FROM sys.columns

WHERE name LIKE '%_commodity_%'

or

SELECT * FROM INFORMATION_SCHEMA.columns

WHERE column_name LIKE '%_commodity_%'

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQL SERVER ROOKIE Ten Centuries Points: 1032 More actions · Answer 2

SQL SERVER ROOKIE

Ten Centuries

Points: 1032

November 29, 2012 at 2:52 pm

#1564060

Thank you Koen 🙂

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

No problem 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

dogramone Hall of Fame Points: 3216 More actions · Answer 4

Get use to using the INFORMATION_SCHEMA version as it is a part of the SQL ANSI Standard whereas MS can modify the system tables with releases and patches INFORMATION_SCHEMA will always be supported.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

This is fairly straightforward

selectobject_name(object_id) AS TableName

, name AS ColumnName

from sys.columns

where name like '%columnname string%'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQL SERVER ROOKIE Ten Centuries Points: 1032 More actions · Answer 6

Thank you guys,And how to look for tables in a DB lets say any table with the word "SHIPPING"

Regards

SM

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

SQL SERVER ROOKIE (11/30/2012)
Thank you guys,And how to look for tables in a DB lets say any table with the word "SHIPPING"
Regards
SM

select * from sys.tables

You should probably become more familiar with the information schema views available.

http://msdn.microsoft.com/en-us/library/ms186778.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

SQL SERVER ROOKIE (11/30/2012)
Thank you guys,And how to look for tables in a DB lets say any table with the word "SHIPPING"
Regards
SM

This

selectobject_name(object_id) AS TableName

from sys.columns

where object_name(object_id) like '%table name string%'

group by object_name(object_id)

order by 1

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Perry Whittle (11/30/2012)
SQL SERVER ROOKIE (11/30/2012)
Thank you guys,And how to look for tables in a DB lets say any table with the word "SHIPPING"
Regards
SM
This
selectobject_name(object_id) AS TableName
from sys.columns
where object_name(object_id) like '%table name string%'
group by object_name(object_id)
order by 1

Not sure why you would use sys.columns to find table names. Why not use sys.tables? You would have to either use a group by or a distinct when looking at the columns or you have duplicates.