September 1, 2005 at 1:09 pm
I know there is an easy way to display all the tables in a database, but I can't seem to find it. Can somebody help?
Thanks!
September 1, 2005 at 1:16 pm
select *
from INFORMATION_SCHEMA.TABLES
where Table_Type = 'BASE TABLE'
SQL = Scarcely Qualifies as a Language
September 1, 2005 at 1:29 pm
Thank you!
September 2, 2005 at 1:42 am
sp_tables @table_type="'TABLE'"
will also work
David
If it ain't broke, don't fix it...
September 2, 2005 at 3:59 am
This one also works
Select name,* from database..sysobjects where type = 'U'
January 24, 2006 at 4:12 pm
Any reason why I should get ",,,BASE TABLE" when I issue a "select * from INFORMATION_SCHEMA.TABLES where Table_Type = 'BASE TABLE'" query? I'm willing to believe that maybe permissions are messed up, but I'm not sure where.
January 24, 2006 at 8:25 pm
Yes. Views are reported as well by INFORMATION_SCHEMA.TABLES. To ensure you only get tables, you must use Table_Type = 'BASE TABLE' or you'll get views, too.
K. Brian Kelley
@kbriankelley
January 24, 2006 at 9:16 pm
Thanks, but... I guess I should rephrase that. When I issue that select statement all I get is a list of identical records, each of which contains three commas and "BASE TABLE". No other information. My guess is that somehow I'm not getting the proper permissions to view the table name itself. Although if that's the case I'm not sure how it came about or how to fix it. The Query Analyzer works fine.
Oh, and this is SQLServer 2000, if that makes a difference.
January 25, 2006 at 6:52 am
Well, in SQL Server 2000 if you have access to the database, you have access to the INFORMATION_SCHEMA views. And they'll review all information to anyone... this gets fixed in SQL Server 2005. So if anything, it should work properly in SQL Server 2000. That query should not be returning what you've gotten. Does it matter what database you are in (it shouldn't)?
K. Brian Kelley
@kbriankelley
January 25, 2006 at 3:08 pm
That was my understanding, too. I'm moving things over from MSAccess, so I'm new to SQL Server and kind of fumbling my way around. No, it doesn't matter what database I connect to - I still can't return any data except the Table_Type qualifier. I would almost suspect that some antivirus protection was messing things up if it weren't for the fact that the Query Analyzer shows me the proper info. And I'm calling the odbc api from C, to get as close to the metal as I can. I'm stumped.
January 26, 2006 at 5:10 am
It seems like you're having some homegrown code receiving the results, perhaps something is amiss there?
How does it look in QA if you (important) log in as the exact same user as when using your app?
Note on INFORMATION_SCHEMA views and permissions:
They will only return info on objects where the current user has permissions.
If, say, you query for tables, only tables where the querying user has permissions will be returned.
This is different from when querying sysobjects for names, then names are returned regardless of if the querying party has any permissions on the actual objects or not.
/Kenneth
January 26, 2006 at 6:34 am
And to add on, in SQL Server 2005 you only see objects you have permissions for regardless of which way you use.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply