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.
120 questions
2
votes
2
answers
157
views
sp_describe_first_result_set with json columns
I'm working on an analyzer that accepts a SQL Server query and returns information about all columns in the first result set - until for clause is used, this is pretty straightforward with ...
- 205
1
vote
0
answers
83
views
Looking for a query to get a view's referenced columns in another database
Getting the referenced columns from a view that references data to it's own database if pretty straightforward with the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE-tables but I'm struggling with getting the ...
- 151
1
vote
1
answer
93
views
How to resolve pg_locks.objid to a meaningful object name
I'm trying to work out what a particular transaction is locking that prevents other queries from executing. I queried pg_locks like this:
select c.relname, l.*
from pg_locks l
join pg_database d on l....
- 250
2
votes
2
answers
321
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
122
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
57
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 ...
- 380
19
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 = '...
- 529
0
votes
2
answers
183
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
444
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,099
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
348
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
95
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 ...
- 121
0
votes
1
answer
262
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
44
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.
...
- 133
2
votes
1
answer
2k
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,220