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.
| Author |
Topic |
|
Sidster
Starting Member
7 Posts |
 Posted - 2007-11-15 : 17:49:39
|
| Hi,I'm new to SQL and would like to write a script that queries all of my tables to find a specific column (say "CustomerID"). I would like to get a report of all tables that use this column.Any suggestions?Thank you in advance.Sid. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-15 : 17:51:17
|
| select table_namefrom information_schema.columnswhere column_name = 'CustomerID'Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Sidster
Starting Member
7 Posts |
Posted - 2007-11-15 : 18:14:30
|
| Wow..that was fast. Thank you.I ran the query but it doesn't return any data. No errors either. I tried my database, master, model, etc. Nada.Suggestions?Sid. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-15 : 18:26:44
|
| Are you sure your column is named CustomerID? Are you sure you looked in the right database? Try this:select *from YourDatabaseNameGoesHere.information_schema.columnswhere column_name like 'c%'Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Sidster
Starting Member
7 Posts |
Posted - 2007-11-15 : 18:32:37
|
ah.I forgot the db name in the FROM command.That's exactly what I needed.Thank you, Goddess.  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-15 : 18:37:35
|
| You don't need to specify it there, but I put it there due to you not seeing any data returned. Next time make sure that you have selected the correct database in the dropdown or put the USE DatabaseName above your query.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Searching all tables for a column