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
2
votes
0answers
27 views

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

Willing to solve a problem related to a wrong value for is_media_read_only database property I did some research and tests, but in the end this question aroused: What exactly triggers the UPDATE of ...
1
vote
1answer
17 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
21 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
24 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
38 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
247 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
43 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
240 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
122 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
60 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
196 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
488 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
54 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 ...
0
votes
1answer
190 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
419 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
41 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 ...
4
votes
1answer
348 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
98 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 ...
0
votes
1answer
704 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
303 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
72 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
67 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 = ...
10
votes
2answers
484 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
386 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. ...
3
votes
2answers
3k 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
1k 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
20 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 ...
2
votes
1answer
5k 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 ...
1
vote
1answer
2k 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 ...
0
votes
1answer
139 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
220 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 ...
6
votes
3answers
7k views

Making sense of sys.objects, sys.system_objects, and sys.sysobjects?

In this question I was writing a query using sys.sysobjects. However, one of the answers mentioned sys.system_objects. I'm just wondering what is the difference between these tables? sys.objects sys....
0
votes
1answer
5k views

Why am I getting “Invalid object name 'sys.sysobjvalues'.” [duplicate]

When I run SELECT OBJECT_DEFINITION(OBJECT_ID('sys.assembly_files')); I can see that there is a self-join on sys.sysobjvalues, but when I try to select that table I get, Msg 208, Level 16, State 1,...
2
votes
3answers
506 views

Why does sqlcmd truncate varbinary(max)?

Inside of the sys.assembly_files catalog, I can see a field named content 1> SELECT assembly_id, LEFT(name, 50), file_id, content FROM sys.assembly_files; 2> GO assembly_id ...
1
vote
1answer
203 views

SQL Server Error “Database 'mssqlsystemresource' does not exist.”

On Linux, whenever I run use mssqlsystemresource to get access to Resource Database, I get an error. How do I resolve this. 1> use mssqlsystemresource 2> go Msg 911, Level 16, State 1, Server ...
6
votes
2answers
886 views

How do you directly modify the system catalogs in SQL Server 2017?

NOTE: I understand the risks involved, and the possibility of destroying a production system by doing this. I'm interested in doing it anyway. Whenever I try to play with system catalogs, I gets ...
3
votes
1answer
7k views

dual table postgres doesn't return timestamp

I am trying to select now() from my local postgres database dual table: select now() from dual; It returns no rows. Just prints column name now(yyyy-MM-dd HH:mm:ss.ffffff) What is it that I am ...
4
votes
1answer
1k views

How list all the indexes along with their type (BTREE, BRIN, HASH etc…)

I'm querying to the system catalog in Postgresql 9.6.4 Getting a result set of tables and their indexes is straight forward,what I'm missing is the index type (BTREE, BRIN, etc..) I can't seem to ...
0
votes
1answer
1k views

Oracle: How to get list of objects accessible to the user?

I want to get list of all objects which is accessible to the login user. The list should include Table, Sequence, Function, Procedure, View, Sequence, Index, Trigger etc. I am using Oracle 11g and ...
2
votes
1answer
86 views

System View Full Qualification Ignored?

Why does full qualification appear to be getting ignored? Here, I USE the database I'm querying, and get the expected results: use [_DBMgt]; GO select s.[name] as [Schema], o.[name] as [...
0
votes
1answer
2k views

Granting minimum select permission on system “sys” tables in every database

Our development team has created some SSRS reports that retrieve metadata only (no 'real' data) from every database. I am trying to lock down the service account running these reports to the minimum ...
1
vote
2answers
162 views

Can `sys.aud$` recycle itself?

In Oracle 11, what happens if I leave sys.aud$ to get bloated up to soak up the whole SYSTEM TABLESPACE? Does it start rewriting the oldest records? Can Oracle be configured to do so? Does it stop ...
3
votes
2answers
103 views

Where are SQL Server catalog views persisted?

It seems that sys catalog views can be found under every database, either built in or user created. Are they all actual view definitions created under each of the database or just "links" to somewhere?...
0
votes
1answer
33 views

HowTo parsed Stored Procs to get a list of tables and columns referenced in WHERE, GROUP BY, AND JOIN clauses

Is there a tool, script, utility that can parse sys.sql_modules and return a list of tables and columns referenced in the WHERE, GROUP BY, and JOIN clauses of Stored Procedures? Example: CREATE PROC ...
5
votes
2answers
1k views

Update statistics on sys.objects

I am working on a query to give me all the database objects that depend directly or indirectly in any level, to a table called dbo.tblborder, which is heavily depended on. However, this question in ...
1
vote
1answer
485 views

What is the system view for sys.syscolpars?

I am checking system base table at https://msdn.microsoft.com/en-us/library/ms179503.aspx and system internal views at https://technet.microsoft.com/en-us/library/ms189600(v=sql.105).aspx For base ...
1
vote
1answer
74 views

Show the number of all tables, views and base tables

How to do that? I was thinking of something like: SELECT COUNT(A.table_name), COUNT(B.table_name) FROM information_schema.tables AS A, information_schema.views AS B; But sadly it gives me bad ...
-3
votes
2answers
525 views

I want to see all tables of db,but no system tables

On a db oracle we have system tables created by oracle and our tables created by us. I want to see our tables on postgresql I use \d on mysql show tables; And on oracle for a similar output?
2
votes
1answer
1k views

How to check if a table exists in Informix

What would be the query to check that a table exists in Informix ?