mdcarr
asked on
How do I force an update of Information_schema.columns?
Hello,
First thank you for your time.
My INFORMATION_SCHEMA.COLUMNS
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
Returns) An empty recordset.
Query 2) select count(*) from INFORMATION_SCHEMA.COLUMNS
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
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
First thank you for your time.
My INFORMATION_SCHEMA.COLUMNS
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
Returns) An empty recordset.
Query 2) select count(*) from INFORMATION_SCHEMA.COLUMNS
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
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
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
... do you need/have view definition privelege
ASKER
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.
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.
select * from INFORMATION_SCHEMA.COLUMNS