Post reply

Querying a database for a list of tables

  • March 6, 2009 at 5:17 am

    #201997

    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

    #954785

    Alright you can use:

    Use 'Databasename'

    GO

    SELECT * FROM sys.tables

    GO

  • March 6, 2009 at 5:48 am

    #954788

    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

    #954809

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 6, 2009 at 6:35 am

    #954821

    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

    #955305

    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

    #956227

    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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply