sys.objects column [Type] weird value 'ST'
Well, I don't know what ST stands for, but based on the naming convention I would have to guess it's some kind of system-generated statistic.
You could check to see if there is, by chance, some kind of text associated with the object if it is a module, e.g.:
SELECT OBJECT_DEFINITION(613577224);
SELECT definition FROM sys.all_sql_modules
WHERE object_id = 613577224;
You could also try:
EXEC sp_help N'dbo._ST_OEA33...';
Finally, you could try to brute force discover this object in any of the catalog views. This script tries to find any view that contains a row where the object_id
column is equal to that value.
CREATE TABLE #v(v sysname);
DECLARE @sql nvarchar(max) = N'', @obj int = 613577224;
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;
If this yields nothing, you could expand it to include all int
-based columns across all catalog views, since sometimes object_id
values are stored in columns with different names, like referenced_major_id
or parent_object_id
, and any results here might yield clues as well.
SET NOCOUNT ON;
CREATE TABLE #v(v sysname);
DECLARE @sql nvarchar(max) = N'', @obj int = 613577224;
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_%';
EXEC sys.sp_executesql @sql, N'@obj int', @obj;
SELECT v FROM #v;
GO
DROP TABLE #v;
ST Objects are Database Table usage Statistics. I was in a situation yesterday were we needed to delete a Schema completely on an Azure SQL Managed Instance. On trying to drop the schema, we were unable to do this, and got some long Error like ST__xxxxxx___Chinesechars..... The reason for this is the tables that were dropped now no longer belonged to a schema and their Statistics still existed in the Managed Instance sys tables.
You have 2 options. Try to drop the statistics before dropping the table using:
DROP STATISTICS [Database].[Schema].[Table];
Or move the statistics to another schema. Not the best, but will get you around the error when dropping a schema.
ALTER SCHEMA [SchemaToMoveTo] TRANSFER OBJECT::[SchemaFrom].[ST__xxxxxx___Chinesechars]
Seems to be a bug within SQL Managed Instances.