March 6, 2009 at 5:17 am
Hi,
I am struggling to determine how I would query a database to return a list of it's tables.
Can someone help please.
I will then need to query the table for a column list - is this possible?
Thanks
Tony
March 6, 2009 at 5:41 am
Alright you can use:
Use 'Databasename'
GO
SELECT * FROM sys.tables
GO
March 6, 2009 at 5:48 am
Go look up INFORMATION.SCHEMA in BOL, both INFORMATION_SCHEMA.COLUMNS and TABLES and see if that might work for you.
March 6, 2009 at 6:20 am
Definately use INFORMATION_SCHEMA system views is the way to go for stuff like this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2009 at 6:35 am
Also keep in mind SQLServer 2005 / 2008 will only show info for objects you are granted to !
Meaning only if you have been granted (implicit or explicit) "view object definition", you 'll get to see any information of that object.
Check out "VIEW DEFINITION Permission " in books online.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 6, 2009 at 3:17 pm
Newbie to newbie -
To view list of tables I run
sp_tables
Let's say there's a table called "ITEMS", to see its columns I run
sp_columns ITEMS
Gets the job done but if there's better code, please post....
BTW I like the site, lots of good information here....
March 9, 2009 at 11:08 am
Fernando,
You actually have a pretty good solution. Unless the table information needs to be further processed, that proc is a quick and dirty way to get the list. The proc is basically the same as doing "Select * from information_schema.tables". The only difference seems to be the "remarks" column from the proc.
If further processing needs to be done, a temp table can be created to store the results of either the InfoSchema view or the proc. Just look up each in BOL and get the datatypes from the BOL entries for the temp table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply