What are the challenges you face when working across database platforms? Take the survey

An unexpected error was encountered while querying the database system tables

An unexpected error was encountered while querying the database system tables (for SqlTable). You may have insufficient permissions, or the database may be in an inconsistent state. You are recommended to use SQL Compare with dbo permissions. You can check the database is in a consistent state by running DBCC CHECKDB.

Original error message: String was not recognized as a valid DateTime.

Query being executed: SQL query: SELECT


os.name AS schemaname,

o.object_id AS id,


p.name AS owner,

ftc.name AS FullTextCatalogName,

CONVERT (bit, CASE WHEN ftc.name IS NULL THEN 0 ELSE 1 END) AS FullTextIndexed,

fti.change_tracking_state AS FullTextChangeTracking,

fti.stoplist_id AS FullTextStoplistId,

fts.name AS FullTextStoplistName,

ds.name AS FileGroup,

dsx.name AS TextFileGroup,

fs.name AS FilestreamFileGroup,

ds.type AS dataspacetype,

ps.data_compression AS data_compression,

ps.partition_number AS data_compression_partition_number,

CONVERT(bit, CASE WHEN ct.is_track_columns_updated_on IS NULL THEN 0 ELSE 1 END) AS TableChangeTrackingEnabled,

ct.is_track_columns_updated_on AS TableChangeTrackingTracksColumns,

o.is_filetable AS IsFiletable,

ft.directory_name AS DirectoryName,

ft.filename_collation_name AS FilenameCollationName,

o.is_memory_optimized AS MemoryOptimized,

o.durability AS Durability,

o.modify_date AS LastModified,

o.is_remote_data_archive_enabled AS IsRemoteDataArchiveEnabled,

rt.is_migration_paused AS IsRemoteDataArchivePaused,

o.temporal_type AS TemporalType,

os2.name AS HistoryTableSchema,

o2.name AS HistoryTableName


sys.tables o WITH (NOLOCK)

LEFT JOIN sys.tables o2 with (NOLOCK) ON o2.object_id=o.history_table_id

LEFT JOIN sys.schemas os WITH (NOLOCK) ON os.schema_id=o.schema_id

LEFT JOIN sys.schemas os2 WITH (NOLOCK) ON os2.schema_id=o2.schema_id

LEFT JOIN sys.database_principals p WITH (NOLOCK) ON p.principal_id=o.principal_id

LEFT JOIN sys.fulltext_indexes fti WITH (NOLOCK) ON fti.object_id=o.object_id

LEFT JOIN sys.fulltext_catalogs ftc WITH (NOLOCK) ON ftc.fulltext_catalog_id=fti.fulltext_catalog_id

LEFT JOIN sys.fulltext_stoplists fts WITH (NOLOCK) ON fts.stoplist_id=fti.stoplist_id

LEFT JOIN sys.indexes ix WITH (NOLOCK) ON ix.object_id=o.object_id

LEFT JOIN sys.data_spaces ds WITH (NOLOCK) ON ds.data_space_id=ix.data_space_id

LEFT JOIN sys.data_spaces dsx WITH (NOLOCK) ON dsx.data_space_id=o.lob_data_space_id

LEFT JOIN sys.data_spaces fs WITH (NOLOCK) ON fs.data_space_id=o.filestream_data_space_id

LEFT JOIN sys.partitions ps WITH (NOLOCK) ON ps.object_id=o.object_id AND ps.index_id <=1

LEFT JOIN sys.change_tracking_tables ct WITH (NOLOCK) ON ct.object_id = o.object_id

LEFT JOIN sys.filetables ft WITH (NOLOCK) ON ft.object_id = o.object_id

LEFT JOIN sys.remote_data_archive_tables rt WITH (NOLOCK) ON rt.object_id = o.object_id

WHERE ((o.is_memory_optimized = 0 AND ix.index_id<2) OR (o.is_memory_optimized = 1 AND ix.index_id = 2))

AND (CAST(CASE WHEN o.is_ms_shipped = 1 THEN 1

WHEN (SELECT major_id FROM sys.extended_properties

WHERE major_id = o.object_id AND minor_id = 0 AND class = 1 AND

name = N'microsoft_database_tools_support') IS NOT NULL THEN 1


END AS bit)=0)

ORDER BY os.name, o.name, ps.partition_number


Sign In or Register to comment.