Stack Overflow
Stack Overflow

Questions tagged [system-tables]

59 questions
0
votes
1 answer

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 there in INDEXES and INDEXCOLUSE tables but many…
CamelCamelius
  • 333
  • 2
  • 15
0
votes
0 answers

does oracle capture ROWID of rows returned for SELECT statements being run on a table?

We have data in Oracle (Exadata) table identified by PK (say id). I have to figure out which users are accessing which rows in this Oracle table. I know that there is way to have Oracle capture information about "which users has accessed a table".…
rp1
  • 159
  • 2
  • 10
0
votes
2 answers

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.
djy
  • 737
  • 6
  • 14
0
votes
2 answers

Find all databases where particular user exists and its role

I have a huge instance containing 1000+ databases. I need to find a way to query entire instance and find databases that contain particular user and what role this user has. I am not interested whether the user is orphanded. I just want to know…
QWE
  • 287
  • 2
  • 15
0
votes
0 answers

Table property 'name' in sys.filegroups is listed as 'Primary' when it is not needed

I am creating a script to create a table. When the table has a large object column such as varchar(max) the table needs the text TEXTIMAGE_ON [PRIMARY]. To determine the name of the file group, I use the system table, sys.filegroups and the column…
Gloria Santin
  • 2,066
  • 3
  • 51
  • 124
0
votes
2 answers

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 ought to be a table named xyz. I receive an…
ferris
  • 55
  • 9
0
votes
2 answers

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 SYSIBM.SYSCOLUMNS where TBNAME='Table1'; Normally the…
Christian
  • 22,585
  • 9
  • 80
  • 106
0
votes
0 answers

How can I know why the particular columns are created and according to what the columns are update in System Base Tables?

I have one doubt related to SQL Server System Base Tables. How can I know why the particular columns are created and according to what the columns are update in System Base Tables? For example in sys.sysschobjs system base table maintaining details…
ANJi
  • 27
  • 8
0
votes
1 answer

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 Cte ON 1 = 1 WHERE Level < 3 ) SELECT * FROM…
user2163043
  • 361
  • 1
  • 3
  • 14
0
votes
2 answers

Read Data from 'MSDB' System Table

In my application I want to retrieve blind_copy_recipients from system table 'sysmail_mailitems' from database 'MSDB' in SQL Server 2012. I am using Entity Framework in a C# web application to query databases. I created a class for sysmail_mailitems…
AM0
  • 165
  • 2
  • 4
  • 20
0
votes
3 answers

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 from sys.database_principals u,…
user2965433
  • 23
  • 6
0
votes
1 answer

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 SELECT permission was denied on the object…
Olivier Faucheux
  • 2,520
  • 3
  • 29
  • 37
0
votes
1 answer

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] to be different than the value in…
Aaron Anodide
  • 16,906
  • 15
  • 62
  • 121
-1
votes
1 answer

Oracle: Grants for select from SYS.DBMS_LOCK_ALLOCATED

I'm wondering do I need just SELECT FROM grant to select from SYS.DBMS_LOCK_ALLOCATED or there is some special role for it? I'm unable to grant SELECT FROM SYS.DBMS_LOCK_ALLOCATED to my test user, so I've tried to grant SELECT FROM ANY TABLE. But it…
Alexander
  • 1
  • 1
1 2 3
4