1

Not quite getting how to enumerate the columns within a specific database and table.

I can list all the databases using:

SELECT name from master..sysdatabases
go

I can show all the tables for a specific database

SELECT TABLE_NAME FROM ValidUsers.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'

I'm then trying to list all the columns for a specific table, which brings up nothing. Im guessing I need to somehow specify the database, but I can't work out how to do it.

select COLUMN_NAME from information_schema.columns where table_name = 'Admins' 
0

1 Answer 1

2

Select the database as the active database-context using use before executing the SELECT-Statement.

So it would:

USE mydatabase;
SELECT column_name FROM information_schema.columns WHERE table_name = 'Admins';

An alternative would be to include the database as a prefix to the information_schema like this:

SELECT column_name FROM [mydatabase].information_schema.columns WHERE table_name = 'Admins';
6
  • information_schema.columns is a reference to database.table so the query is self-sufficient Commented Jul 21, 2016 at 8:51
  • I'm not a hundred percent sure what you mean by that but I did edit my answer having another look at it. Was the possibility to prefix the information_schema with the database what you wanted to point out?
    – Seth
    Commented Jul 21, 2016 at 12:54
  • Sorry for being obscure: I meant to point at USE being useless if you already reference the database information_schema in the SELECT query. But as I see your answer was accepted, so it had probably done some magic trick :) Commented Jul 21, 2016 at 13:07
  • 1
    The problem is that the information_schema.columns "Returns one row for each column that can be accessed by the current user in the current database." (Emphasis added). As such you won't get any results if you don't somehow set a DB context. For this you'd (from my knowledge) have to either USE or prefix the database name. At least if it's not part of the default context for the user.
    – Seth
    Commented Jul 22, 2016 at 6:13
  • Well, I don't know how I read mysql in the question in the first place, sorry for messing things up. Commented Jul 22, 2016 at 7:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .