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.
117 questions
2
votes
2
answers
117
views
Azure MS SQL: grant sys.* and msdb.dbo.* select permissions for Grafana monitoring
I'm trying to monitor Azure MS SQL databases using this Grafana dasboard. It executes several queries to tables whose names start with sys and msdb.dbo.
I've created a dedicated grafana user for this ...
- 141
0
votes
1
answer
87
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.[...
0
votes
0
answers
32
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 ...
- 362
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 = '...
- 519
0
votes
2
answers
149
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
- 33
3
votes
1
answer
379
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.
...
- 1,109
2
votes
1
answer
2k
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 ...
- 24.3k
3
votes
1
answer
321
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 @...
- 292
1
vote
1
answer
69
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 ...
- 123
0
votes
1
answer
227
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 ...
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.
...
- 123
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 ...
- 3,180
3
votes
1
answer
113
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, ...
- 185
0
votes
0
answers
83
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 ...
- 267
-1
votes
1
answer
325
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: '/...
2
votes
1
answer
120
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 ...
- 578
1
vote
1
answer
372
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 ...
- 906
0
votes
1
answer
197
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):...
- 55
2
votes
1
answer
409
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
1
answer
317
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 ...
- 853
0
votes
1
answer
656
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),
@...
- 455
2
votes
1
answer
266
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).
...
4
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, ...
- 41
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 ...
- 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....
- 3
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 ...
- 243
0
votes
0
answers
210
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 ...
- 39.5k
2
votes
1
answer
109
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 ...
- 39.5k
0
votes
1
answer
453
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 ...
- 79
2
votes
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 ...
- 39.5k
8
votes
2
answers
450
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 ...
- 5,926
1
vote
1
answer
499
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
1
answer
826
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
1
answer
186
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 ...
- 117
2
votes
1
answer
696
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 ...
- 1,063
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,
...
- 5,512
0
votes
1
answer
235
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 ...
- 195
1
vote
1
answer
66
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 ...
- 185
5
votes
1
answer
5k
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 ...
- 39.5k
3
votes
1
answer
919
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 ...
- 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 ...
- 201
0
votes
1
answer
953
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 ...
- 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
...
- 227
0
votes
1
answer
167
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
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
- 2,152
5
votes
2
answers
678
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 ...
- 2,152
1
vote
2
answers
144
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 ...
- 64.7k
8
votes
2
answers
2k
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 ...
- 449
-1
votes
2
answers
772
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,152
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 ...
- 2,152