Skip to main content

Questions tagged [system-tables]

System tables are tables included behind the scenes by vendor RDBMS systems. For example, these are the tables found in master, msdb and model in sql server. They also appear when features such as cdc, merge replication, and other optional features are installed. This tag is for questions specific to system tables.

Filter by
Sorted by
Tagged with
0 votes
0 answers
32 views

Calculating min/max values of a columnstore table with sys.column_store_segments

I need to fetch the max and min values of an arbitrary column on a table. The table has a clustered columnstore-index, billions of rows, and hundreds of columns. I'd like to leverage that fact that ...
Derek's user avatar
  • 15
0 votes
1 answer
69 views

Metadata query slow on one specific database

I have quite a weird problem (who doesn't). Our ETL Software scans source databases to claim object data. It extracts information from INFORMATION_SCHEMA about objects (tables and views) SELECT t.[...
LonelyDBA92's user avatar
0 votes
0 answers
19 views

Mysql system tables showing incorrect cardinality for columns

I’m new to mysql. I have encountered a weird problem on our prod database. We have a table with primary key a and an index on some other b column. Below’s the query: select b from my_table where a ...
Shiwangini's user avatar
18 votes
1 answer
1k views

DISTINCT not reducing two equal values to one

Can anyone explain the below situation, where two seemingly equal values are not reduced by DISTINCT? The query above is SELECT DISTINCT name FROM master.sys.dm_os_spinlock_stats where name = '...
jimbobmcgee's user avatar
0 votes
2 answers
136 views

Name in sys.availability_groups_cluster returns double character length

'select name, len(name) as length from sys.availability_groups_cluster' Returns some double values: name length AG_QBUS_MFG01 13 AG_QBUS003 20 TEST_ONLY 18
CrushingIT's user avatar
3 votes
1 answer
325 views

sys.dm_exec_describe_first_result_set returns wrong data type

I have two SQL Servers 2016 connected with Linked Server. When I query sys.dm_exec_describe_first_result_set from the remote server, it returns numeric datatype for column table instead of decimal. ...
Muflix's user avatar
  • 1,109
1 vote
1 answer
1k views

DBCC CLONEDATABASE returns error: Cannot insert duplicate key row in object 'sys.syssingleobjrefs

I'm trying to run the following line of code: DBCC CLONEDATABASE ([DBName],[DBName_Staging]) WITH VERIFY_CLONEDB And I'm getting the following error: Msg 2601, Level 14, State 1, Line 15 Cannot insert ...
Kenneth Fisher's user avatar
4 votes
1 answer
308 views

How to decrypt the name of columns from view?

I had to deal with a third-party database, I am trying to get all columns used in a view but I got them encrypted e.g: instead of getting int for NUMERO_SALARIE I got TNumSal why? DECLARE @...
XMehdi01's user avatar
  • 302
1 vote
1 answer
66 views

Constantly querying system views [Oracle, MSSQL, PostgreSQL]

I'm a kind-of-experienced database developer that just got into the DBAdministration world. I've been told that in both Oracle and MSSQL is definitely a terrible idea to run queries such as the below ...
glezo's user avatar
  • 121
0 votes
1 answer
200 views

Reorg SYSIBM tables to reduce extents (XT) - change priqty & secQTY

I have a new DB2 V12 database on z/OS 2.4 z13, with hundreds of programs to rebind, such as: sysibm.syspackage, sys.columns, sys.tables, etc. These will grow, hence the need to reorganize the ...
TechnoCaveman's user avatar
0 votes
0 answers
30 views

Is there an equivalent to EVENTDATA in SQLite?

I am looking for ways to create a log table in my SQLite database, where I'm able to track changes that have been made. One fellow has beaten me to it here, if someone is willing to give it a go. ...
manoelpqueiroz's user avatar
2 votes
1 answer
1k views

OBJECT_ID(...) function vs join on sys.tables and sys.schemas

When querying metadata for a specific table, I can either use the OBJECT_ID function or join the relevant system tables (sys.tables and sys.schemas). For example, both of those queries will return ...
Heinzi's user avatar
  • 3,155
3 votes
1 answer
112 views

System names of returned columns

I'm looking for a way to obtain system names of columns returned by an arbitrary sql query using Sql Server 2019. Here are a few sample queries and desired results: company: id, name employee: id, ...
Matěj Štágl's user avatar
0 votes
0 answers
71 views

Is there an Euler diagram of SQL Server 'sys' tables?

I am trying to wrap my head around which tables to include in queries on the sys tables because it sounds like some objects might appear in multiple places according to the summary descriptions that ...
adolf garlic 's user avatar
-1 votes
1 answer
311 views

unable to open database because of corrupt file

when ever i try to open my database i get an error saying SQL> alter database open; alter database open * ERROR at line 1: ORA-01147: SYSTEM tablespace file 13 is offline ORA-01110: data file 13: '/...
xzgtrtprmwteaghbai's user avatar
2 votes
1 answer
113 views

What are the undocumented values for referencing_class in sys.sql_expression_dependencies?

In SQL Server, the sys.sql_expression_dependencies system catalog view "Contains one row for each by-name dependency on a user-defined entity in the current database", to quote the docs. One ...
AakashM's user avatar
  • 578
1 vote
1 answer
330 views

Mapping logins to linked servers for migration

My team is migrating a SQL 2012 server with an extensive list of linked servers to SQL 2016. My task is to script the creation of linked servers on the new server. It looks like I have all the ...
Metaphor's user avatar
  • 906
0 votes
1 answer
185 views

Oracle 11gR2 - How To Audit SYS.USER$ Table

I have a Oracle 11gR2 database and I want to audit all select operations on SYS.USER$ table for which users select on this table. When I try this command (I'm trying to run this command with SYS user):...
jrdba's user avatar
  • 55
2 votes
1 answer
351 views

fulltext_stoplists to database_principals issue

When I run a DBCC check against the database, it informs me that there is an issue with the sys.fulltext_stoplists object. It appears that the principal_id in stoplists does not have a relationship ...
CalgaryCoolCat's user avatar
0 votes
1 answer
290 views

Revoke access to selected system tables

In Vertica, the SELECT privilege on all system tables in V_CATALOG and V_MONITOR is granted to PUBLIC. Therefore, by default all users can query them. And not all system tables seem appropriate to be ...
prototype's user avatar
  • 853
0 votes
1 answer
620 views

Permission to select sys tables in stored procedure

I have implemented the stored procedure to check if a specific [schema].[procedure] exists. CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName varchar(128), @...
Circle Hsiao's user avatar
2 votes
1 answer
252 views

Performance of query against column level dependencies much faster when using deprecated system view

Consider these two queries that get the names of triggers dependant on the JV_VoyageFlight column in table dbo.JobVoyage. the first uses the 'old' mechanism that is deprecated (sys.sql_dependencies). ...
WiseTechGlobal CTO's user avatar
3 votes
2 answers
2k views

PostgreSQL 9.6.12 autovacuum constantly running on system tables

I come from a SQL Server, Oracle, Sybase DBA background but I am now looking into an AWS Aurora cluster running PostgreSQL 9.6.12 and have noticed something which I think is odd, but maybe it's not, ...
dodgybugga's user avatar
2 votes
1 answer
2k views

T-SQL query to show table definition of sys.time_zone_info

Following query could be used to show definitions of custom table. SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH , IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE ...
WXJ96163's user avatar
  • 281
0 votes
1 answer
1k views

Select Column data, from sys.column info

Either this is impossible, or I can't write a good internet search. I have a query to get the table and column information for our database: SELECT t.name [table], c.name [column] FROM sys....
CervonMi's user avatar
0 votes
0 answers
52 views

MSSQL Old system tables: is there an hidden synonym? or multiple names? [duplicate]

I have tried to create new table named 'SysObjects' on dbo schema here is an example: CREATE TABLE sysobjects (Column1 INT ); SELECT * FROM sysobjects WHERE 1 = 0; SELECT * FROM dbo.sysobjects WHERE ...
mordechai's user avatar
  • 243
0 votes
0 answers
200 views

Why would sys.dm_exec_requests show a query in Database A blocking a query in Database B when neither query references the same tables?

I have a query I use in-house to monitor slow running processes in the data layer. It essentially looks at a few fields from the following system tables and views joined in the following way: SELECT ...
J.D.'s user avatar
  • 38.3k
2 votes
1 answer
103 views

Are stored procedure query texts stored in any tables?

I don't readily see anything in the sys schema objects, other than the DM_EXEC_SQL_TEXT function that requires a plan handle, but the procedures I'm trying to get their query texts from aren't ...
J.D.'s user avatar
  • 38.3k
0 votes
1 answer
427 views

In which system table does Postgres store FK cardinality?

I'm trying to reverse engineer a set of PG databases into a Data Model. I've found everything I need to define all the sub-models, tables, columns and FK constraints, but can't find anywhere that ...
khafka's user avatar
  • 79
1 vote
1 answer
1k views

Does querying the sys entities and DMVs lock the underlying tables and what happens when SQL Server tries to write to them?

If I do a vanilla SELECT * FROM sys.tables or SELECT * FROM sys.dm_exec_query_stats are those tables getting locked since I'm reading data from them? If so, what happens when SQL Server ties to write ...
J.D.'s user avatar
  • 38.3k
8 votes
2 answers
439 views

What exactly triggers the UPDATE of the column is_media_read_only on sys.database_files?

Willing to solve this problem related to a wrong value for is_media_read_only database property I did some research and tests, but in the end I couldn't sort out what exactly triggers the UPDATE of ...
Ronaldo's user avatar
  • 5,636
1 vote
1 answer
419 views

How I can find out the foreign wrapper that a foreign table belongs to?

I have found that the following query lists all my foreign tables in my PostgreSQL database: select * from information_schema."tables" where table_type='FOREIGN TABLE'; Now I want to find out the ...
Dimitrios Desyllas's user avatar
1 vote
1 answer
791 views

List Postgresql foreign data wrappers without the use of the `psql` command

As seen in a stackoverflow answer I can use the psql command and the \dew[+] command in order to list the foreign wrappers. But the use of universal database tools such as DBEaver, DataGrid, etc ...
Dimitrios Desyllas's user avatar
1 vote
1 answer
139 views

SQL Server 2016 - Disparity Between View Definition and View Design

I've run into an interesting issue with some databases I've inherited, and could use some help unpacking it all. The Issue After adding a column to a table, views selecting * from that table are now ...
Vocoder's user avatar
  • 117
2 votes
1 answer
637 views

Object id error in sql server

I want to know the object referenced as an associatedObjectId in a deadlock graph, so I ran the below query: SELECT OBJECT_NAME(1234560112364076) I get the following error: Arithmetic overflow ...
SQL_NoExpert's user avatar
  • 1,061
7 votes
2 answers
1k views

How to find if a column is referenced in a computed column?

I'm trying to mass retype columns. That means first dropping and recreating any constraints they are part of. I found columns referenced by these constraints Foreign Keys, Primary Keys, Indexes, ...
Zikato's user avatar
  • 5,102
0 votes
1 answer
219 views

How to get MSSQL Type definition from system tables?

If I have a user table type (like CREATE TYPE ...), how do I get to it's definition in the system tables? I know I can see it through SSMS through GUI, but I would like to have a query to retrieve ...
veljkoz's user avatar
  • 195
1 vote
1 answer
65 views

Mimicking cascading foreign key referencing system table

I'd like to create a table to store some additional metadata about tables, but I'm running into a number of roadblocks since foreign keys and triggers cannot be used with system tables. On INSERT it ...
user3747260's user avatar
5 votes
1 answer
4k views

What's the values for the Freq_Interval column in MSDB.dbo.SysSchedules when Freq_Type is weekly and more than one day is selected in the schedule?

I'm reading up on the SysSchedules table in the Microsoft API, and am trying to incorporate using this table in a query. In the documentation, the Freq_Interval column seems to be defined out for ...
J.D.'s user avatar
  • 38.3k
3 votes
1 answer
881 views

Causes for pg_class locks except DROP TABLE

I do experience the following situation on a PostgreSQL 11: A transaction with unknown statements creates an ACCESS SHARE lock on pg_class. Another connection wants to execute DROP TABLE which causes ...
Sebastian's user avatar
  • 131
2 votes
2 answers
2k views

When is the modify_date of an object updated?

As part of the daily checks I'm setting up, I'm restoring the nightly full backups to our test server and checking the modified date of objects. This is not for any kind of precise change tracking or ...
BWFC's user avatar
  • 201
0 votes
1 answer
900 views

Update sys.job$ set this_date=:1 where job=:2 as one of the most time-consuming queries - oracle

I am currently helping some organization with their Oracle database performance. They are using Oracle 11gR2 Standard Edition in Windows 2008. As there are no enterprise performance packages ...
aLuViAn's user avatar
  • 193
5 votes
1 answer
2k views

Why sys.columns.is_nullable can be null?

I'm writing a query that will be mapped to a C# class, and when getting the type of the query's columns, it results that some columns in sys.columns can be NULL is_nullable is_replicated ...
Enrique Zavaleta's user avatar
0 votes
1 answer
166 views

Excluding Read only Log backups using ola-hallengren script

I know that Full backups are excluded from readable secondaries, but how do I exclude log backups from read-only secondaries? The is_read_only column in sys.databases does not get set on ...
MonteK's user avatar
  • 1
1 vote
1 answer
1k views

Authentication type differences in SQL Server

I want to know what is the difference between authentication types(NONE, WINDOWS, INSTANCE) in sys.database_principals table. I can't find clear explanation in documentation. Thanks
igelr's user avatar
  • 2,142
5 votes
2 answers
667 views

Why public is not mentioned as a fixed role in system database in SQL Server?

While running select * from sys.server_principals for public role, in the column is_fixed_role is shown 0 But the documentation states that it is a fixed server role. Documentation Link Although ...
igelr's user avatar
  • 2,142
1 vote
2 answers
139 views

Does MySQL have a more private and complete catalog other then INFORMATION_SCHEMA?

Most databases provide the standardized INFORMATION_SCHEMA, however most also provide something else, In PostgreSQL we have the system catalog which is pg_catalog In SQL Server, you have the System ...
Evan Carroll's user avatar
  • 63.9k
8 votes
2 answers
1k views

What is the persistent_version_store table?

What is the purpose of the MS Shipped table: sys.persistent_version_store? Why do I want to know Primarily curiosity, partly because it may relate to an issue waiting to bite us... We're running a ...
JohnLBevan's user avatar
-1 votes
2 answers
757 views

MS SQL Server differentiate system and user defined indexes using sys.indexes table

I am trying to write a sproc to maintain all the indexes created by me. My indexes are shown in random numbered rows in sys.indexes table, mixed with system indexes. Now I want to write a select ...
igelr's user avatar
  • 2,142
3 votes
1 answer
3k views

MS SQL Server sys.indexes vs sys.sysindexes system table difference

For indexes there are 2 system tables: sys.indexes and sys.sysindexes. I'd like to know what are their differences. Both contain information about indexes then why they are separate? Thanks in ...
igelr's user avatar
  • 2,142