Database Administrators Stack Exchange
Database Administrators Stack Exchange

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
28
votes
8 answers

How to query a database for empty tables

Due to some 'developers' we had working on our system we have had issues with empty tables. We have found that during the transfer to the cloud several tables were copied, but the data in them wasn't. I would like to run a query the system tables…
codehammer
  • 473
  • 2
  • 5
  • 5
23
votes
2 answers

Referencing system views in SSDT?

I've imported a database into SSDT containing a reference to a system view (specifically, sys.columns). The problem is, that I then get warnings about unresolved references when I build the project From what I've seen on MSDN forums, it looks like…
TomGough
  • 281
  • 2
  • 8
19
votes
1 answer

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 = 'SBS_UCS_DISPATCH'; The equivalent SELECT name FROM…
jimbobmcgee
  • 529
  • 2
  • 11
16
votes
3 answers

How to 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 find the type of index anywhere in the system…
maxTrialfire
  • 1,194
  • 4
  • 11
  • 23
16
votes
2 answers

Can SQL Server system tables be defragmented?

We have several databases in which a large number of tables are created and dropped. From what we can tell, SQL Server does not conduct any internal maintenance on the system base tables, meaning that they can become very fragmented over time and…
Geoff Patterson
  • 8,447
  • 2
  • 28
  • 53
15
votes
2 answers

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??). CREATE OR REPLACE FUNCTION column_exists(_tbl…
tinlyx
  • 3,810
  • 14
  • 50
  • 79
13
votes
2 answers

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 more details. Issue at hand: at a strange hour…
Marian
  • 15,741
  • 2
  • 62
  • 75
12
votes
3 answers

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.system_objects sys.sysobjects sysobjects has…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
12
votes
1 answer

Can I Improve Performance on Bloated System Tables?

Background: I have numerous databases with a large number of VIEW's, and an extremely large number of SYNONYM's. For example, one db has more than 10k VIEW's and 2+ million SYNONYM's. General Problem: Queries involving sys.objects (and system…
Dave Mason
  • 875
  • 1
  • 8
  • 19
10
votes
2 answers

Finding all joins required to programmatically join a table

Given a SourceTable and a TargetTable, I would like to programmatically create a string with all joins required. In short, I am trying to find a way to create a string like this: FROM SourceTable t JOIN IntermediateTable t1 on t1.keycolumn =…
Metaphor
  • 906
  • 1
  • 11
  • 30
8
votes
5 answers

Capturing datetime of change in SQL Server CDC

So we've started exploring using change data capture on one of our production databases. We'd like to know the datetime of each change. Reading through walkthrough's and tutorials etc it seems that the standard approach is to use the LSN to relate…
RThomas
  • 3,446
  • 6
  • 30
  • 48
8
votes
2 answers

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 the column is_media_read_only on…
Ronaldo
  • 6,017
  • 2
  • 13
  • 43
8
votes
2 answers

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, Check constraints, Rules, Default constraints. But…
Zikato
  • 5,619
  • 1
  • 17
  • 34
8
votes
2 answers

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 SaaS instance of MS Dynamics 365 for Finance and…
JohnLBevan
  • 459
  • 1
  • 4
  • 15
7
votes
2 answers

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 these weird errors, UPDATE sys.sql_logins SET…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
1
2 3 4 5 6 7 8