Post reply

information_schema.tables

  • December 18, 2003 at 11:01 am

    #83616

    Select * from information_schema.tables

    The above query is working fine,

    But when i describe the same table like

    sp_tables information_schema.tables, it is giving the following error

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near '.'.

    Anybody knows what is the reason, what is the significance of '.' there ?

  • December 18, 2003 at 11:15 am

    #487020

    Try

    master..sp_tables tables, information_schema

    The views only exist in the Master db, but will use the sysobjects from the currnt db.



    Once you understand the BITs, all the pieces come together

  • December 18, 2003 at 11:56 am

    #487028

    Keep in mind the information schema views are there to meet SQL-92 compliance and provide metadata about the database structure. The "." means you have a two-part naming convention. As in:

    [owner] "dot" [object]

    Like:

    dbo.Customers

    The table is Customers, its owner is dbo.

    K. Brian Kelley, GSEC

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • December 18, 2003 at 12:12 pm

    #487032

    To investigate the above, I took a look at the definitions of both the information_schema.tables VIEW, and the sp_tables SP in the Master db. This helps me understand the parameters sp_tables requires, and noticed it allowed for table owner as the second parameter.



    Once you understand the BITs, all the pieces come together

  • December 18, 2003 at 9:59 pm

    #487144

    Thanks. this is a new information for me,

    thanks thanks a lot

Viewing 5 posts - 1 through 5 (of 5 total)

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