gotchasqlMinor
sys.objects column [Type] weird value 'ST'
Viewed 0 times
objectscolumnweirdvaluetypesys
Problem
I am seeing a strange (not-documented) value for [Type] column in sys.objects. The value is "ST" as shown in the below (note, dbo.Record is a user table)
Does anyone know what this 'ST' value means? (This is in SQL Server 2014 Developer Edition)
Does anyone know what this 'ST' value means? (This is in SQL Server 2014 Developer Edition)
Solution
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.:
You could also try:
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
If this yields nothing, you could expand it to include all
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;Code Snippets
SELECT OBJECT_DEFINITION(613577224);
SELECT definition FROM sys.all_sql_modules
WHERE object_id = 613577224;EXEC sp_help N'dbo._ST_OEA33...';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;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;Context
StackExchange Database Administrators Q#191111, answer score: 4
Revisions (0)
No revisions yet.