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
1answer
54 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
37 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
40 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
203 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
24 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
27 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
26 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
52 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
300 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
48 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
428 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
200 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
112 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
266 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
597 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
61 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
268 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
479 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
42 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
1answer
453 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
106 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 ...
1
vote
1answer
890 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
416 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
98 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
74 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
588 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
523 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
4k 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
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 ...
4
votes
1answer
7k 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
3k 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
179 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
266 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
8k 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
6k 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
625 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
229 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
1k 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
8k 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
2k 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
87 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 [...
1
vote
1answer
3k 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
180 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
109 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
34 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
535 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 ...