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 ...

learn more… | top users | synonyms

1
vote
1answer
143 views

Migration from Cassandra 2.x to 3.x: schema_keyspaces table is gone. How do you query system keyspaces?

I just migrated from Cassandra 2.x to 3.x (specifically, 3.4). Now the system tables have completely changed, and these changes are not in the documentation. If you look at the current CQL docs for ...
13
votes
2answers
349 views

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 ...
10
votes
1answer
238 views

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 ...
0
votes
1answer
51 views

Retrieve linked servers out of system database files?

I have the data and log files of a SQL-Server that has crashed. It's user databases have been restored. I am looking for the jobs and linked servers that have been there before the crash. I attached ...
2
votes
2answers
73 views

What is the meaning of hbcolid field?

I just connect to SQL Server and invoke the following command: select * from sys.sysrscols The result table contain a field hbcolid, which seem to be identical to rscolid. I think rscolid is surely ...
0
votes
1answer
53 views

SYSAUX used space close to 100%

I am using Oracle 11g database server. I checked the SYSAUX used space using Object Browser, and it shows 99%. What will happen if it reaches 100%?
2
votes
1answer
56 views

Partition scheme filegroup metadata

When you create a partition scheme against a particular partition function, you specify a series of filegroups. I cannot find the metadata store where this filegroup list can be found & queried. ...
1
vote
1answer
83 views

'cmd' Field Definitions sysprocesses MS SQL Server

I am looking at doing some profiling of connections types and volumes across the day. I am using the sys.sysprocesses to gather the information I require, looking specifically at the 'cmd' field to ...
0
votes
0answers
38 views

Recover Oracle database with damaged system datafile

I'm working with Oracle version 11. My system01.dbf is corrupted and there is no backup. The database was in archive log mode and logs exists. Is there any way to recover data and import to a new ...
2
votes
1answer
43 views

Permissions to be granted to developers to investigate jobs, queries, and packages

I have just installed a new sql server 2014 server, migrated some databases there and connected some applications to them. These servers have also SSAS and SSIS. I have a group of developers there ...
5
votes
2answers
4k views

Netezza: What schema contains all the system tables / views?

Is there a schema I can look in or a query that I can run to see all the Netezza system or metadata tables? I know about _v_table, _v_view, _v_relation_column, and _v_groupusers. I know there are ...
1
vote
1answer
24 views

Recovering credential data from a master DB backup

On a SQL 2008 R2 instance, a misconfigured credential used as a SSIS proxy for an SSA job was dropped before I could look at the system views to figure out when it was originally misconfigured. I ...
2
votes
2answers
248 views

List the underlying objects used in a SQL Server Function

I use the below to get a list of underlying objects in a View: SELECT view_name, Table_Name FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE WHERE View_Name IN ( 'View1', 'View2' ) ORDER BY ...
4
votes
1answer
213 views

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 ...
3
votes
2answers
410 views

Getting all databases which a sql logon can connect

I need to know all databases in a SQL Server on which a user can connect. With sys.databases I get all databases and with sys.server_principals or sys.syslogins I get all server logons. But I can't ...
0
votes
0answers
49 views

Forcing FK constraint to validated in system catalog

I have a big table (~60gigs, 35mill+ rows) that I need to add a FK to. Adding a FK directly to the table causes a validation which runs for about 30secs that I want avoid as it's a 30sec access ...
2
votes
1answer
212 views

Operator “~<~” uses varchar_pattern_ops index while normal ORDER BY clause doesn't?

Let's say I have a table with 1 000 000 records. Structure of table is: create table individual ( id serial primary key, surname varchar(128), "name" varchar(128), patronymic varchar(...
0
votes
1answer
367 views

Is there are a way to restore System schema to it's original state?

I've installed Oracle 11g Database Enterprise Edition and my task was to import schemas. I'm totally new so I ran command: imp system/password file=path.dmp full=y And import everything (10 dumps, ...
4
votes
3answers
253 views

How to find what frequent and/or large transaction have taken place in MS SQL Server?

Each day is experiencing about 1GB sum total of all the transaction log backups that day, while at the end of that week the Full DB backup is about 180MB. For a business database for buying/selling ...
3
votes
0answers
171 views

ASSIGN value for cmd in sysprocesses? What does it mean?

We're seeing some strange blocking behaviour in a production (SQL Server 2008 R2) system that we're investigating, and it seems to have one consistent and novel element: in the sysprocesses table, the ...
1
vote
0answers
140 views

SQL Server 2005 CHECKCATALOG fails

CHECKCATALOG is failing on a database that originated in SQL Server 2000. I am getting errors of the type: Msg 3853, Level 16, State 1, Line 1 Attribute (object_id=489156888) of row (object_id=...
2
votes
2answers
4k views

How list of netezza system tables/views?

Is ther a system table/view in Netezza wich permits to show the number of records for each table ? Thanks
0
votes
1answer
49 views

Possible Inconsistency Among System Tables

Is there a reason that there would be a significant difference in the number of records in the DBA_USERS table and the USER$ table?
0
votes
1answer
75 views

PHYSICAL_READ_BYTES_DELTA and PHYSICAL_READ_BYTES_TOTAL

What is the difference between PHYSICAL_READ_BYTES_DELTA column and PHYSICAL_READ_BYTES_TOTAL column in DBA_HIST_SQLSTAT table? Are they correlate? Please provide also an example.
0
votes
1answer
35 views

SQL Server 2005 Whole-Migration, which databases should I recreate?

In relation to my question about SQL Server 2005 migration procedure, I am re-creating the databases on the new server. I noted that the old server has the following databases (outside of System ...
2
votes
4answers
2k views

Oracle How to find Debug Compiled Stored Procedures/Functions

How can I get list of stored procedures/Function which are compiled for debug? I do not see relevant column in following tables. DBA_PROCEDURES DBA_OBJECTS
1
vote
2answers
415 views

Table not dropped correctly in SQL Server Express 2008

I've dropped a table but it is still listed in sys.tables. What's more confusing is that I can create the table again as one user, but if I log in as another user with the same permissions, I can't (I ...
1
vote
2answers
3k views

How to get columns from system tables or views like sys.database_files (SQL Server 2012)

How can I write a query to return all columns of, for example, the sys.database_files table? I see many suggestions for using information_schema.columns for getting table names for user databases/...
1
vote
1answer
3k views

Is there a way to determine automatic storage table spaces?

First off, we are on both Windows and AIX and are using DB2 ESE 9.7 FP 4. I am trying to determine dynamically whether or not table spaces are set up via automatic storage or not (so we can ...
0
votes
1answer
4k views

Oracle: list user-created tables in the SYS schema

I need to delete all tables (hundreds) in the SYS schema that someone accidentally created with sqlplus. Looking in dba_tables and dba_objects, I don't see a way to distinguish these tables from pre-...
20
votes
7answers
16k views

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....
3
votes
2answers
500 views

Adding an index to a system catalog in Postgres

I'm having a situation very similar to the one described here: I've got a SaaS situation where I'm using 1000+ schemas in a single database (each schema contains the same tables, just different ...
5
votes
4answers
9k views

DBCC SHRINKFILE works with file_id but not with logical name

I am trying to shrink a database file but am running into errors. Using the file_id from sys.database_files works, but using the logical file name yields an error. The logical file name is the same ...
6
votes
1answer
2k views

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 ...
3
votes
1answer
1k views

Different Results From sys.databases As Different Users

We have a SQL Agent job that is configured to run as user "dbo". This job gets a list of databases defined in sys.databases that match a naming scheme. The job then performs some dynamic SQL to ...
7
votes
3answers
2k views

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 ...