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
0answers
37 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 ...
-1
votes
1answer
31 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: '/...
0
votes
0answers
94 views

How to delete specific range of data from Service Broker > System Queues data table?

Due to sudden increase of my database .mdf file we found there are 4 internal queue message table are increase unexpectedly. so we decided to delete some range of data from these table. we try this ...
2
votes
1answer
35 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 ...
0
votes
1answer
40 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 ...
0
votes
1answer
61 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):...
2
votes
1answer
57 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 ...
0
votes
1answer
34 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 ...
0
votes
1answer
75 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), @...
2
votes
1answer
67 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). ...
2
votes
2answers
221 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, ...
1
vote
1answer
266 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 ...
0
votes
1answer
74 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.tables t ...
0
votes
0answers
50 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 ...
0
votes
0answers
62 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 ...
2
votes
1answer
62 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 ...
-1
votes
1answer
172 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 ...
1
vote
1answer
174 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 ...
8
votes
2answers
311 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 ...
1
vote
1answer
31 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 ...
1
vote
1answer
114 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 ...
1
vote
1answer
31 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 ...
2
votes
1answer
131 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 ...
6
votes
2answers
532 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, ...
0
votes
1answer
56 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 ...
1
vote
1answer
52 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 ...
5
votes
1answer
1k 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 ...
3
votes
1answer
305 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 ...
2
votes
2answers
563 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 ...
0
votes
1answer
552 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 ...
5
votes
1answer
971 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 ...
0
votes
1answer
89 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 ...
1
vote
1answer
484 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
5
votes
2answers
560 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 ...
1
vote
2answers
46 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 ...
5
votes
2answers
758 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 ...
-1
votes
2answers
147 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 ...
2
votes
1answer
1k 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 ...
1
vote
1answer
841 views

Where in pg_catalog is the database or catalog information stored about relations?

When I query a table like information_schema.tables SELECT table_catalog, table_schema, table_name FROM information_schema.tables; I get a result like this, table_catalog | table_schema ...
0
votes
1answer
185 views

Inconsistent presence of view in Postgres system tables

I am perplexed here. I have two environments, let's say TEST and PROD. In both environments' Postgres DBs there is a view foo_view visible in my DB client (DBeaver). However, only in PROD can I find ...
0
votes
1answer
117 views

SQL Server Agent appears to be treating sysprocesses (and dm_exec_sessions) differently

I have a watchdog process that runs via a SQL Agent job to manage some custom tasks. One of the checks is that the process no longer exists: UPDATE R SET IsOrphaned = 1, DebugTimestampUtc = ...
11
votes
2answers
1k views

Never ending Query Store search

I'll say from the beginning that my question/problem looks similar to this previous one, but since I'm not sure if the cause or the starting info is the same, I decided to post my question with some ...
1
vote
1answer
955 views

How to check if a PostgreSQL table is temporary?

How can I write a query to check if a PostgreSQL (10.x) table is a temporary table? I looked at the pg_class system table, but couldn't find any column about whether the relation is temporary. ...
5
votes
2answers
8k views

How to get the schema name of a table of type regclass in PostgreSQL?

In writing a function to test if a column col_name exists in a table _tbl, I'd like to extract the table's schema name, which is passed into the function as a regclass parameter (for security??). ...
1
vote
3answers
2k views

Find a specific table and column value across all databases

This is a question about logic that could help me understand more how to iterate into my DBs. On my server I have several AdventureWorks* databases: I have a query that cold help me iterate through ...
0
votes
1answer
27 views

Noob question about user accounts

I'm new to this whole database thing. Got a uni project which I was hoping y'all could help with. So I'm creating a webapp that is essentially an online art gallery store. The database will contain ...
5
votes
1answer
11k views

postgres system catalog query columns from table

I am building an integration test against an app that provisions a database for a user. The User created for it is not a super user, and does not have access to schema_information.tables because when ...
2
votes
1answer
5k views

How do I query INFORMATION_SCHEMA.COLUMNS on Amazon RDS?

I received a list of column names from another team. To ensure all field column names are valid inside a specific schema, I put them into a temporary table. I'm now wanting to see if they exist. When ...
1
vote
1answer
283 views

ORA-00932 - Error when I try to filter all_triggers

I'm trying to execute the next query: SELECT * FROM all_triggers t WHERE UPPER(trigger_name) like UPPER('%ABC%') AND UPPER(TRIGGER_BODY) LIKE '%DEF%'; But I get the following error: ORA-00932: ...
2
votes
1answer
357 views

How does the sys.objects Catalog View pull from itself? [duplicate]

I'm using this stored procedure called OBJECT_DEFINITION as was suggested. It's helping me wrap my head around the SQL Server system. However, there is one area that I can't yet figure out. On this ...