Stairway to Exploring Database Metadata Level 1: Why Should You Care About the Dynamic Online Catalog?

  • Comments posted to this topic are about the item Stairway to Exploring Database Metadata Level 1: Why Should You Care About the Dynamic Online Catalog?

    Best wishes,
    Phil Factor

  • The next to last section of code is truncated. There is a SELECT with no FROM. Working out the FROM is easy enough but are there other queries missing from that example?

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

  • Great training article, thank you.

  • @Bryant

    Now fixed. Thanks for letting me know.

    Best wishes,
    Phil Factor

  • Hi all images are broken

  • Nice article! Several image links is broken. In all samples display the schema(i.e object_schema_name(ob.object_id) + '.' + ob.name as User_Table), and describe the importance to always use schema name in conjunction with object name.

  • Several image links are broken.   Can someone look into this?

  • It is now January 2021, and the image links are still not corrected.  Nearly 5 years!!!! This is why SQL Server Central is losing relevance and is becoming just another internet equivalent of a landfill.

  • I just noticed that User Defined Table Types do not show up in sys.objects by  the name you have given them, you have to look at sys.all_objects and Name LIKE 'TT<Your Type>%'  or the Type_Table_Object_ID = Object_ID to find the actual object.

    And surprise, surprise - the object is under the 'sys' schema.

    -- Find ID in sys.table_types

    SELECT *
    FROM sys.table_types
    WHERE Name = '<Your Type>';
    -- Entry in sys.objects

    SELECT *
    FROM sys.objects
    WHERE Name LIKE 'TT%<Your Type%' -- or OBJECT_ID =  column value under Type_Table_Object_ID from above;

    And, oddly, note that the definition in sys.objects has the is_ms_shipped flag set to 1 ! Not intuitive.

    Seems like a flaw.

    Doug

     

  • I just noticed that User Defined Table Types do not show up in sys.objects by  the name you have given them, you have to look at sys.all_objects and Name LIKE 'TT<Your Type>%'  or the Type_Table_Object_ID = Object_ID to find the actual object.

    And surprise, surprise - the object is under the 'sys' schema.

    -- Find ID in sys.table_types

    SELECT *
    FROM sys.table_types
    WHERE Name = '<Your Type>';
    -- Entry in sys.objects

    SELECT *
    FROM sys.objects
    WHERE Name LIKE 'TT%<Your Type%' -- or OBJECT_ID =  column value under Type_Table_Object_ID from above;

    And, oddly, note that the definition in sys.objects has the is_ms_shipped flag set to 1 ! Not intuitive.

    Seems like a flaw.

    Doug

     

  • I just noticed that User Defined Table Types do not show up in sys.objects by  the name you have given them, you have to look at sys.all_objects and Name LIKE 'TT<Your Type>%'  or the Type_Table_Object_ID = Object_ID to find the actual object.

    And surprise, surprise - the object is under the 'sys' schema.

    -- Find ID in sys.table_types

    SELECT *
    FROM sys.table_types
    WHERE Name = '<Your Type>';
    -- Entry in sys.objects

    SELECT *
    FROM sys.objects
    WHERE Name LIKE 'TT%<Your Type%' -- or OBJECT_ID =  column value under Type_Table_Object_ID from above;

    And, oddly, note that the definition in sys.objects has the is_ms_shipped flag set to 1 ! Not intuitive.

    Seems like a flaw.

    Doug

     

  • I just noticed that User Defined Table Types do not show up in sys.objects by  the name you have given them, you have to look at sys.all_objects and Name LIKE 'TT<Your Type>%'  or the Type_Table_Object_ID = Object_ID to find the actual object.

    And surprise, surprise - the object is under the 'sys' schema.

    -- Find ID in sys.table_types

    SELECT *
    FROM sys.table_types
    WHERE Name = '<Your Type>';
    -- Entry in sys.objects

    SELECT *
    FROM sys.objects
    WHERE Name LIKE 'TT%<Your Type%' -- or OBJECT_ID =  column value under Type_Table_Object_ID from above;

    And, oddly, note that the definition in sys.objects has the is_ms_shipped flag set to 1 ! Not intuitive.

    Seems like a flaw.

    Doug

     

  • I just noticed that User Defined Table Types do not show up in sys.objects by  the name you have given them, you have to look at sys.all_objects and Name LIKE 'TT<Your Type>%'  or the Type_Table_Object_ID = Object_ID to find the actual object.

    And surprise, surprise - the object is under the 'sys' schema.

    -- Find ID in sys.table_types

    SELECT *
    FROM sys.table_types
    WHERE Name = '<Your Type>';
    -- Entry in sys.objects

    SELECT *
    FROM sys.objects
    WHERE Name LIKE 'TT%<Your Type%' -- or OBJECT_ID =  column value under Type_Table_Object_ID from above;

    And, oddly, note that the definition in sys.objects has the is_ms_shipped flag set to 1 ! Not intuitive.

    Seems like a flaw.

    Doug

     

  • I just noticed that User Defined Table Types do not show up in sys.objects by  the name you have given them, you have to look at sys.all_objects and Name LIKE 'TT<Your Type>%'  or the Type_Table_Object_ID = Object_ID to find the actual object.

    And surprise, surprise - the object is under the 'sys' schema.

    -- Find ID in sys.table_types

    SELECT *
    FROM sys.table_types
    WHERE Name = '<Your Type>';
    -- Entry in sys.objects

    SELECT *
    FROM sys.objects
    WHERE Name LIKE 'TT%<Your Type%' -- or OBJECT_ID =  column value under Type_Table_Object_ID from above;

    And, oddly, note that the definition in sys.objects has the is_ms_shipped flag set to 1 ! Not intuitive.

    Seems like a flaw.

    Doug

     

  • I just noticed that User Defined Table Types do not show up in sys.objects by  the name you have given them, you have to look at sys.all_objects and Name LIKE 'TT<Your Type>%'  or the Type_Table_Object_ID = Object_ID to find the actual object.

    And surprise, surprise - the object is under the 'sys' schema.

    -- Find ID in sys.table_types

    SELECT *
    FROM sys.table_types
    WHERE Name = '<Your Type>';
    -- Entry in sys.objects

    SELECT *
    FROM sys.objects
    WHERE Name LIKE 'TT%<Your Type%' -- or OBJECT_ID =  column value under Type_Table_Object_ID from above;

    And, oddly, note that the definition in sys.objects has the is_ms_shipped flag set to 1 ! Not intuitive.

    Seems like a flaw.

    Doug

     

Viewing 15 posts - 1 through 15 (of 21 total)

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