Questions tagged [system-tables]
The system-tables tag has no usage guidance.
60
questions
1754
votes
36
answers
4.0m
views
Find all tables containing column with specified name
Is it possible to query for table names which contain columns being
LIKE '%myName%'
- 29.2k
343
votes
15
answers
526k
views
How to check if a Constraint exists in Sql server?
I have this sql:
ALTER TABLE dbo.ChannelPlayerSkins
DROP CONSTRAINT FK_ChannelPlayerSkins_Channels
but apparently, on some other databases we use, the constraint has a different name. How do I ...
- 25.6k
7
votes
2
answers
10k
views
Difference between SYS.ALL_TAB_COLUMNS and SYS.ALL_TAB_COLS in Oracle 12c
What is the difference between the ALL_TAB_COLUMNS and ALL_TAB_COLS system tables in Oracle 12c? In my DB, the ALL_TAB_COLUMNS has slightly fewer rows than ALL_TAB_COLS.
- 30.5k
6
votes
2
answers
9k
views
PostgreSQL: find information about user defined types
Where is information about user-defined types stored?
Are there some tables which contain information about the fields of a user-defined composite type, their names, etc.?
- 421
6
votes
1
answer
2k
views
What's the difference between name and tbl_name in sqlite_master?
What's the difference between name and tbl_name in sqlite_master?
3
votes
2
answers
4k
views
What is database objects are needed for a Database Design in SQL Server 2008 R2?
I have a Visual Studio project where I have scripted out my dbo.sysdiagrams. When I deploy my project the whole database is dropped and recreated from the scripts I have. One of my post deploy ...
- 80.3k
3
votes
1
answer
16k
views
How to extract Stored Procedure body in Teradata
I'm trying to extract Stored procedure DDL by querying system tables.
If I run the following query
select * from dbc.tvm where TableKind = 'P'
both fields RequestText and CreateText contain NULL.
Is ...
- 373
3
votes
1
answer
745
views
How can I rename a user-created SYSUSERS table?
Someone renamed our Users table to SYSUsers, which is a key sql table name. Is there a way to rename this table?
I've tried right-clicking the table and going to rename, and running sp_RENAME on it, ...
- 131k
2
votes
3
answers
6k
views
Unable to update sys.columns - any other approach?
I just found out, that since I created my DB on a different server that had a different collation, then immigrated to a new server with new collation, now I keep on having trouble, and I decided to ...
- 103k
2
votes
3
answers
2k
views
Ms Sql sys tables equivelant in Oracle
I could not find all tables equivalent in oracle.
Such as;
sys.index_columns
sys.columns
sys.foreign_key_columns
Is there any list to find out these table names? We have several queries in mssql ...
- 1,202
2
votes
1
answer
15k
views
Which permission need to grant to access sys.dba_systems
I am working on the application which works on Oracle. For some kind of logic I need to get the list of tables from the given db user with the specified schema. In my case, I have a user which have ...
- 2,228
2
votes
1
answer
429
views
Programmatically generate DDL statement for User Defined Data Type (UDDT)
I'm trying to add faking synonyms pointing to remote objects support(PR) for tSQLt.
To make it possible to mock/fake tables that have user defined system types in the table definitions, I need to ...
- 5,577
2
votes
3
answers
1k
views
Oracle db view/system table to check if given part of a package is a Procedure or a Function
I am using the Oracle system tables to get some metadata about the Packages in our database.
Here is one of my queries:
select AP.PROCEDURE_NAME
from ALL_PROCEDURES ap
where ap.object_name = :...
- 22.3k
2
votes
1
answer
3k
views
List Tables and Table Extended Properties
I want to return a list of all tables within my database along with any related extended properties for the table.
This gives me the list of tables:
SELECT TABLE_NAME AS tTableName, TABLE_TYPE ...
- 95
2
votes
1
answer
779
views
Which postgres system table stores a map of PIDs to session authorizations?
I want to look further into the PIDs listed in pg_stat_activity to know what their last succesful call to SET SESSION AUTHORIZATION set their session authorization to. Which table or view do I need ...
- 16.1k
2
votes
1
answer
120
views
Mapping to different linked servers per database
I have an MSSQL Server 2008 database "DBLive" that link to three different external servers [ExtServer1, ExtServer2, ExtServer3], all defined in sys.servers. There are several stored procedures that ...
- 1,164
2
votes
0
answers
3k
views
The newly created schema and tables are not listed under "Information_schema.columns" table in PostgreSQL
We have newly created few tables and schema(with tables under it). We used to fetch the table+ columns info by querying Information_schema.columns table. But the newly created tables and schema are ...
- 115
1
vote
3
answers
808
views
Why there is a difference between sp_helpfile vs sys.master_files?
Hi while restoring database using restore command in sql server to specific drive, after executing sp_helpfile its showing incorrect file path, however sys.master_files showing right file path ?
why ...
- 5,322
1
vote
2
answers
5k
views
mysql: Cant we create triggers on system tables?
I am trying to audit the privilege changes in mysql.user table by writing a trigger on it.
insert trigger: will capture who gave the new permissions and when
update trigger: will capture who changes ...
- 1,490
1
vote
1
answer
206
views
How to get the name of the domain that is assigned to a specific field/column of a table?
I use a select from StackOverflow: How can I get the table description (fields and types) from Firebird with dbExpress to get schema-data of Firebird databases.
However, this select does not show the ...
1
vote
1
answer
491
views
How to get the SPS number from a SAP HANA database?
I'm currently working on a script which writes metadata to system tables of an SAP HANA database.
For one example, I need to differentiate between a HANA database with an Support Package Stack of ...
- 13
1
vote
2
answers
77
views
SQL - Group count by 2 or more columns using union takes more than 2 seconds
Given a table, let's call it performance6a -
with table structure >>
student_id | math | history | language | science
table content - performance6a
I need to write a query which produces a ...
1
vote
1
answer
207
views
SQL Server to check all columns in a databases views for a specific string value
My employer has a third party application that has a very complex set of obliquely named views. I am trying to locate the views that contain specific data that is entered in the applications UI.
I ...
- 13
1
vote
1
answer
2k
views
How to get proper column types in Sybase
I have to know the data type of some columns in my table in Sybase ASE. Here is my query
select name from systypes
where type in (39,
47,
39,
39,
106
)
this returns
char
varchar
sysname ...
- 7,888
1
vote
2
answers
125
views
bobj system tables
I am new to BOBJ reporting and I am trying to generate a data dictioanry for BOBJ reports , like what universe does a report look into, what object does a report column refer and what tabel in the DB ...
1
vote
1
answer
1k
views
What privileges do you need to access sys.$ tables?
What privileges do you need to be able to query the sys.obj$, sys.col$ etc system tables directly?
- 45.8k
1
vote
1
answer
174
views
How to find when was table Last Truncated in Sybase? Any system table containing that Info
Hi All Sybase Experts And Coders,
I have a strange requirement to find out when my Tables in database was last truncated? Is there any way to find out using any of the systemtables in sybase.
If ...
- 11
1
vote
1
answer
358
views
Including system tables in SQL Server Management Studio generated scripts
I'm currently facing a problem when scripting my development db with SQL Server Management Studio.
I'm developing an application using EF5 code first and I'm constantly moving my development db from ...
- 391
1
vote
2
answers
4k
views
SQL Server meta data table and column descrption [duplicate]
Possible Duplicate:
SQL Server: Extract Table Meta-Data (description, fields and their data types)
I just like to ask if there is a way to programmatically retrieve table and column descriptions ...
- 35
1
vote
2
answers
489
views
MS Sql Server Object Creation / Alter Script
In Sql Server when you select to modify a SP or UDF it loads the objects alter query in a query window. Are these queries accessible in a system table so I can store the alter statements somewhere? It ...
- 9,843
0
votes
2
answers
4k
views
SYSIBM.SYSCOLUMNS query shows columns twice if using REMARKS
I've noticed a strange behaviour while working with DB2 and the SYSIBM.SYSCOLUMNS which only appears in one table.
This is my query:
select distinct NAME, COLTYPE, LENGTH, SCALE, REMARKS from ...
- 22.3k
0
votes
2
answers
6k
views
Recreate a `mysql.user` table in mysql
I found my mysql.user table is messed up and use drop table if exists mysql.user to drop it, how can I create this special table manually and add my new account to it.
- 757
0
votes
2
answers
119
views
SQL Server system table identity crysis / SYSOBJECTS table ID is close to int max value
SYSOBJECTS table has an identity column of int type. But values in ID column are getting close to int max value. I guess it'll become impossible to create any objects when it reaches its maximum.
Is ...
- 91
0
votes
1
answer
546
views
How does view name get out of sync with view definition?
I tracked down a bug in my system to this anomaly - at least it's an anomaly in my system of 15 catalogs with similar but unequal schemas.
What causes the [TABLE_NAME] in [INFORMATION_SCHEMA].[VIEWS]...
- 17k
0
votes
1
answer
3k
views
How to join System tables or Information Schema tables with User defined tables in Redshift
I have a requirement where I need to join Redshift Information schema table with User defined table. I have tried below queries and getting mentioned errors.
select *
from pg_table_def a join ...
- 2,288
0
votes
2
answers
754
views
How to tell whether a column is of sysname type
It's a common question how to find the columns in a table by querying the system columns. For example SQL server query to get the list of columns in a table along with Data types, NOT NULL, and ...
- 1,400
0
votes
2
answers
73
views
SQL Server querying system objects
I have a stored procedure in my Master Database. It is used by external programs which provide all of the parameters. One of the parameters provided is a database name. Within that database there ...
- 55
0
votes
1
answer
209
views
sys.objects.type_desc column won't match with anchor in recursive CTE
I have the following CTE:
;WITH Cte([Type], [Level]) AS
(
SELECT CONVERT(NVARCHAR(60), 'USER_TABLE'), 1
UNION ALL
SELECT CONVERT(NVARCHAR(60), o.type_desc), Cte.[Level] + 1
FROM sys.objects o
JOIN ...
- 371
0
votes
3
answers
1k
views
Long term supported method to check 'db_owner' role for a user
I need to check whether a user is db owner.
I've found two methods:
look at "exec sp_helprolemember" output table
select count(*) from (select DbRole = g.name, MemberName = u.name, MemberSID = u.sid ...
- 123
0
votes
2
answers
173
views
how to add new column in pg_class for object creation date dumping
I want to create a column in pg_class for object creation dumping but I am not able to add.
I am using below query:
ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated TIMESTAMP;
when I run this ...
0
votes
1
answer
783
views
SQL Anywhere query to dig schema structure
I'm trying to make a SQL query which does the following things:
Select all tables in a schema
Select all columns from the table
Select the columns type (char, int, tinyint, etc)
Select the columns ...
- 358
0
votes
1
answer
1k
views
How to select database name in sys.dm_*
I'm calling stored proc through a service. I wanted my stored proc to be running on a database.
I've two questions
Is sys.dm_* tables specific to a database or it is general one?
select * FROM sys....
- 101
0
votes
2
answers
1k
views
Extended Events connection_id vs client_connection_id
Hello guys I want to find a way to identify a query executed for Extended Events in Microsoft SQL Server (to filter the Extended Event with only that executed query)
If i query the system views in SQL ...
user8554358
0
votes
1
answer
240
views
DB2 table's UNIQUE INDEX not showing in TABCONST and CONSTDEP tables
I am trying to query the unique indices from SYSCAT tables for some user tables using the query below which uses 4 catalog tables (INDEXES, INDEXCOLUSE, TABCONST, CONSTDEP). I realized all indices are ...
- 333
0
votes
1
answer
4k
views
SQL Server: cannot access system tables
I have an MSSQL database and a "normal" user accessing it. When I try to use tables like sys.objects, I become a permission error:
select name from sys.objects
Msg 229, Level 14, State 5, Line 1
The ...
- 2,548
0
votes
0
answers
111
views
SQL Server 2019 - suspicious values in sys.sm_exec_sessions.last_request_end_time
We continuously gather data from sys.sm_exec_sessions and sys.dm_exec_requests to be able further investigate our locking and performance issues.
In some cases, I can see that multiple queries to sys....
- 33
0
votes
1
answer
165
views
Netezza to Redshift Conversion - _v_table and _v_relation_columns alternatives
What are the alternatives for the below Netezza Tables and columns that I could use in Redshift?
_v_table (columns used: 'RELVERSION' and 'OBJTYPE')
_v_relation_columns (columns used: 'NAME' and '...
0
votes
0
answers
1k
views
How can I replace master..sp_values (used for months) on this query?
I'm using master..spt_values within a SQL Server that support is, using it as "months" numbers support (don't ask me why; probably old query write on-the-fly):
SELECT
months.number as ...
- 47.7k
0
votes
0
answers
209
views
Equivalent of SQL Server's SYSPROCESS in POSTGRESQL
I have a query in SQL Server:
SELECT kpid,hostprocess
FROM master..sysprocesses
I want to know the equivalent code of this in POSTGRESQL.
Thanks!
- 15
0
votes
1
answer
674
views
Teradata - How to get number acces by table and by month
I am tring to get an user number per table and per month.
I am trying to get read access (select and no update delete)
Do you know if it is possible to get this with view like dbc.tables ?
Database ...
- 1