Post reply

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

  • January 12, 2023 at 7:21 pm

    #4136954

    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

     

  • January 12, 2023 at 7:21 pm

    #4136956

    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

     

  • January 12, 2023 at 7:21 pm

    #4136957

    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

     

  • January 12, 2023 at 7:21 pm

    #4136958

    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

     

  • January 12, 2023 at 7:21 pm

    #4136960

    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

     

  • January 12, 2023 at 7:21 pm

    #4136959

    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

     

  • January 12, 2023 at 7:21 pm

    #4136961

    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 7 posts - 16 through 21 (of 21 total)

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