Post reply

Schemas associated with Tables in Sysobjects

  • August 5, 2008 at 2:21 am

    #195333

    Hi there

    I am trying to generate code using the information in the sysobjects table.......without going into unnecessary detail I want to generate codet to read "select * from schemaname.tablename"

    Problem - I can get a list of all the tables in sysobjects, but I cannot find the associated schema for each table...??????

    Any ideas?

    Many thanks in advance

    Regards

    Chris

  • August 5, 2008 at 2:30 am

    #853049

    You need to join sys.objects with sys.schemas.

    Something like this

    SELECT s.name + '.' + o.name FROM sys.objects o

    JOIN sys.schemas s ON o.schema_id = s.schema_id

    [font="Verdana"]Markus Bohse[/font]

  • August 5, 2008 at 3:06 am

    #853060

    Try Using INFORMATION_SCHEMA.TABLES

    It contains the tablename and the schema.

    It also contains the list of views in it with TABLE_TYPE = 'VIEW'

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

  • August 5, 2008 at 3:08 am

    #853062

    Because you are using SQL Server 2005, do not use sysobjects. Use the SQL Server 2005 system views, like Markus did in his response. For sysobjects the new system view is sys.objects. You can read more about the new system views on http://msdn.microsoft.com/en-us/library/ms187997.aspx

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 5, 2008 at 7:12 am

    #853162

    The SYSOBJECTS table is really a SQL2000 compatibility view and cannot represent everything in SQL2005 100% correctly. In particular, it cannot represent the SQL2005 separation of Schemas & Owners that did not exist in SQL2000. Thus, even joining with sys.Schemas, you will not always get the right answer.

    As others have mentioned, what you need to do is either use sys.Objects and join with sys.Schemas, or use the INFORMATION_SCHEMA.TABLES view (my preference).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 8, 2008 at 8:45 am

    #855256

    Hi Everyone

    Thanks very much for your help - exactly what I was looking for!

    Regards

    Chris

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

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