I want to create a query that generates the table name. I tried something like this :
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='mytableName'
but it return an empty query. So , any ideas ? Thx
Asked
Active
Viewed 46 times
0
Attila Naghi
- 2,535
- 6
- 37
- 59
-
`WHERE AND` isn't going to run, surely? – Colm Prunty Dec 13 '13 at 13:32
-
it return the same empty query without and, srry my mistake – Attila Naghi Dec 13 '13 at 13:39
-
I updated my answer. You should try TABLE_SCHEMA='dbo', it looks like your schema 'mytableName' is really empty. – Valin Dec 13 '13 at 13:42
-
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='mytableName' => This solves the problem, thx u guys – Attila Naghi Dec 13 '13 at 13:43
-
Am I missing something or would this just return 'myTableName' every time? What's the point? – Colm Prunty Dec 13 '13 at 14:16
3 Answers
0
There is incorrect syntax at "WHERE AND".
You should execute:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='mytableName'
You should try TABLE_SCHEMA='dbo', it looks like your schema 'mytableName' is really empty.
Valin
- 2,285
- 1
- 15
- 10
0
Why WHERE AND TABLE_SCHEMA='mytableName'
The AND is invalid at this point.
Besides the Tablename is in the column TABLE_NAME not TABLE_SCHEMA, maby you should try to filter using the schema name instead of the table name like:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='YOUR_SCHEMA_NAME'
Or if you need information regarding a specific table:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='YOUR_TABLE_NAME'
CloudyMarble
- 36,908
- 70
- 97
- 130
0
To start with: that query is invalid (remove the AND).
2nd it's empty because there are no tables within the schema (also called database) named mytableName.
Also take a look at SHOW TABLES (documentation) and SHOW DATABASES (documentation)
Peter van der Wal
- 11,141
- 2
- 21
- 29