none
Cannot drop schema because of "ST" objects

    Question

  • Hi, 

    in trying to find a bypass solution for the bug I described here, I run into another issue, being that the temporary schema that I created (called "temp") cannot be dropped because it contains 3 objects of the undocumented "ST" type.

    When I try to drop the schema, the following error is displayed: (including the Chinese (?) characters

    DROP SCHEMA [temp];
    
    Msg 3729, Level 16, State 1, Line 262
    Cannot drop schema 'temp' because it is being referenced by object '_ST_6C720D29_00000001_000025E3..........龜祀ɔ.ᾈ曈翽...	...攣翽...攰撻翽.祀ɔ....	...祀ɔ......翽...'.
    When I check in sys.objects what objects are part of the "temp" schema, this result is shown: 

    Does anybody have any idea what these object are and how to get rid of them?

    It is now impossible to drop the (not really) empty temp-schema.

    Thanks,

    Charles


    • Edited by Charles F Tuesday, May 14, 2019 4:10 PM typo
    Tuesday, May 14, 2019 3:34 PM

All replies

  • Hi Charles, here's a post where Aaron Bertrand supplied a few queries to try to get more info where type = 'ST'. Can you run these to see if it provides anything else that might help narrow down the issue?

    https://dba.stackexchange.com/questions/191111/sys-objects-column-type-weird-value-st

    Thanks,
    Sam Lester (MSFT)


    https://blogs.msdn.microsoft.com/samlester/

    Twitter - @SQLSamLester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, May 14, 2019 4:43 PM
    Moderator
  • Hi Sam,

    I ran the scripts that Aaron provided, but thjey don't give any additional info.
    These are the results:

    --Check 1
    --*******
    select *
    from sys.objects o
    where SCHEMA_NAME(schema_id) = 'temp'
    --> returns resultset as described in the screenshot in the first post.
    
    GO
    
    --Check 2
    --*******
    SELECT OBJECT_DEFINITION(231932148);
    SELECT OBJECT_DEFINITION(327932490);
    SELECT OBJECT_DEFINITION(1915414143);
    --> ALL NULLS
    
    GO
    
    --Check 3
    --*******
    SELECT definition FROM sys.all_sql_modules WHERE object_id = 231932148;
    SELECT definition FROM sys.all_sql_modules WHERE object_id = 327932490;
    SELECT definition FROM sys.all_sql_modules WHERE object_id = 1915414143;
    --> no definitions found
    
    GO
    
    --Check 4
    --*******
    CREATE TABLE #v(v sysname);
    
    --DECLARE @sql nvarchar(max) = N'', @obj int = 231932148;
    --DECLARE @sql nvarchar(max) = N'', @obj int = 327932490;
    DECLARE @sql nvarchar(max) = N'', @obj int = 1915414143;
    
    SELECT @sql += N'INSERT #v 
        SELECT DISTINCT ''sys.' + name + N''' 
          FROM sys.' + QUOTENAME(name) + N' 
          WHERE [object_id] = @obj;'
      FROM sys.all_views AS v
      WHERE EXISTS 
      (
        SELECT 1 
          FROM sys.all_columns AS c
          WHERE c.name = N'object_id' 
          AND c.[object_id] = v.[object_id]
      ); 
    
    EXEC sys.sp_executesql @sql, N'@obj int', @obj;
    
    SELECT v FROM #v;
    
    DROP TABLE #v;
    --Returns (for all 3 objects):
    --****************************
    --sys.objects
    --sys.all_objects
    
    GO
    
    
    --Check 5
    --*******
    SET NOCOUNT ON;
    
    CREATE TABLE #v(v sysname);
    
    --DECLARE @sql nvarchar(max) = N'', @obj int = 231932148;
    --DECLARE @sql nvarchar(max) = N'', @obj int = 327932490;
    DECLARE @sql nvarchar(max) = N'', @obj int = 1915414143;
    
    SELECT @sql += N'INSERT #v 
        SELECT DISTINCT ''sys.' + v.name + N''' 
          FROM sys.' + QUOTENAME(v.name) + N' 
          WHERE ' + QUOTENAME(c.name) + N' = @obj;'
      FROM sys.all_views AS v
      INNER JOIN sys.all_columns AS c
      ON v.[object_id] = c.[object_id]
      WHERE v.[schema_id] = 4
        AND c.[system_type_id] IN (56,127)
        AND v.name NOT IN (N'syscolumns')
        AND v.name NOT LIKE N'dm_fts_%'; 
    
    PRINT @SQL
    --EXEC sys.sp_executesql @sql, N'@obj int', @obj;
    
    SELECT * FROM #v;
    
    GO
    DROP TABLE #v;
    --Returns (for all 3 objects):
    --****************************
    --sys.sysobjects (this is the only extra feedback)
    --sys.objects
    --sys.all_objects
    
    GO
    

    As you can see above, only the last check returned an extra system-table where the object can be found in, being sys.sysobjects.

    In the sys.sysobjects table, all fields have value "0" except for fields: (value between brackets)

    • name ("_ST_6C720D29_00000003_00005260")
    • id ("1915414143")
    • xtype ("ST") 
    • uid ("5")
    • crdate ("2019-05-10 17:15:33.227")
    • type ("ST")
    • refdate ("2019-05-10 17:15:33.227")

    I'm still puzzled...

    Cheers,

    Charles


    Tuesday, May 21, 2019 3:38 PM
  • sys.sysobjects is just a compatibility view; an old version of sys.objects so to speak.

    I'm curious, what does datalength (name) return for these three? What do you see if you cast name to binary?

    I have no idea what these ST objects might be. But I note that one of them is created four days later than the other two.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, May 21, 2019 9:19 PM