?
Solved

How do I force an update of Information_schema.columns?

Posted on 2007-08-08
6
Medium Priority
?
3,751 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:mdcarr
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19654815
what about this:

select * from INFORMATION_SCHEMA.COLUMNS where UPPER(TABLE_NAME) = 'CONTACTS'
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 19654824
second potential problem:
do you run the query while being in the database where the table is located?
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 19654832
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19654835
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 19654969
... do you need/have view definition privelege
0
 

Author Comment

by:mdcarr
ID: 19726231
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question