How do I force an update of Information_schema.columns?

Hello,

First thank you for your time.

My INFORMATION_SCHEMA.COLUMNS appears to need updating.

I have an Sql Server 2005 database with about 30 tables in it.  One of the tables is called 'Contacts' and it has 25 columns.

When I execute the following queries from within Sql Server Management Studio, I get the following results:

Query 1) select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= 'Contacts'
Returns) An empty recordset.

Query 2) select count(*) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= 'Contacts'
Returns) 0

Query 3) select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
Returns) MSreplication_options, spt_fallback_db, spt_fallback_dev, spt_fallback_usg, spt_monitor, spt_values.  And that's it!  My tables are not listed in the INFORMATION_SCHEMA.COLUMNS table!

What did I do wrong?  Where's my data?  Is there a way to force an update of the INFORMATION_XXX tables?

Thank you,
Michael
mdcarrAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
second potential problem:
do you run the query while being in the database where the table is located?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:

select * from INFORMATION_SCHEMA.COLUMNS where UPPER(TABLE_NAME) = 'CONTACTS'
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
you should make sure that you run the above query on the database where u created the table

for example ,try this code

USE TempDB
GO
CREATE TABLE Contacts(i int)

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Contacts'
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
another gotcha:
does the account from which you query the information_schema view has at least select permissions to that table?
if it has not, it will not "see" that table
0
 
LowfatspreadCommented:
... do you need/have view definition privelege
0
 
mdcarrAuthor Commented:
First, thank you all for responding so quickly.  Secondly, pardon me for not updating this question till now.

The answer to my problem was that I forgot the 'USE <database>' statement.  I didn't know that if the Use statement was omitted the query defaults to master.

Because you all came back with answers within minutes and were all instrumental in helping me resolve this, I will split the points.

Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.