Search code examples
sqlsql-serverdatabase-schema

Reading a table's column names in SQL Server, problem with INFORMATION_SCHEMA.COLUMNS

I'm trying to read names of columns of a table in SQL Server (later to be used in C#). The solution seems to be:

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = N'my_table_name'

It worked fine yesterday! but at the moment I can't get correct result. Because when I run the whole query it returns an empty table (columns' headers with no value) and when I run this

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

What I get doesn't contain the actual TABLE_NAMEs that I'm looking for. In the result, I get a table (query result) with these names in the column TABLE_NAME (instead of my actual tables' names):

  • spt_fallback_db
  • spt_fallback_dev
  • spt_fallback_usg
  • spt_monitor spt_values

The values for the result column TABLE_CATALOG is always "master" and for the TABLE_SCHEMA column is "dbo". The result is like this:

TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME .....
-------------------------------------------
    master    |     dbo      | spt_monitor
-------------------------------------------
    master    |     dbo      | spt_values
                   .....

I think instead of reading the list of tables, it reads some other root-kind things.

Solution

  • you have to run query like this.

    USE <YourDBName>;
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS