20 votes
Accepted

How to list all the indexes along with their type (BTREE, BRIN, HASH etc...)

The types of indexes in Postgresql are stored in the pg_am catalog table. So to get a list of all tables and their indexes with the index type (or access method ("am") as PostgreSQL calls it) you can ...
maxTrialfire's user avatar
  • 1,084
16 votes
Accepted

DISTINCT not reducing two equal values to one

It looks like the DISTINCT gets optimised out during simplification. select distinct name FROM master.sys.dm_os_spinlock_stats OPTION (QUERYTRACEON 8606,QUERYTRACEON 3604) Gives *** Input Tree: *** ...
Martin Smith's user avatar
  • 84.4k
13 votes

Making sense of sys.objects, sys.system_objects, and sys.sysobjects?

As noted in my previous post sys.sysobjects is deprecated: Note taken from sys.sysobjects (Transact-SQL) This SQL Server 2000 system table is included as a view for backward compatibility. We ...
John K. N.'s user avatar
  • 17.6k
13 votes

How to get the schema name of a table of type regclass in PostgreSQL?

To further simplify, you could use a cast to regnamespace - another object identifier type introduced with Postgres 9.5 SELECT relnamespace::regnamespace::text FROM pg_catalog.pg_class WHERE oid = ...
Erwin Brandstetter's user avatar
11 votes
Accepted

How do you directly modify the system catalogs in SQL Server 2017?

!!   BE SURE TO READ THE WARNING, IN BOLD, AT THE BOTTOM   !! The server configuration option of allow updates was rendered non-functional starting in SQL Server 2005. The documentation ...
Solomon Rutzky's user avatar
11 votes
Accepted

Can SQL Server system tables be defragmented?

Are you sure you have positively and accurately identified this system table as the sole source of "unnecessary pressure on the buffer pool and also negatively impacts the performance of operations ...
Aaron Bertrand's user avatar
9 votes

Can SQL Server system tables be defragmented?

Based on guidance from Aaron's answer as well as additional research, here is a quick write-up of the approach I took. From what I can tell, the options for inspecting fragmentation of system base ...
Geoff Patterson's user avatar
9 votes
Accepted

How to get the schema name of a table of type regclass in PostgreSQL?

Can I just implement this function with e.g. split_part(_tbl::text, '.', 1)? I'm not sure that would be safe. Instead, I would use, SELECT nspname FROM pg_catalog.pg_class AS c JOIN pg_catalog....
Evan Carroll's user avatar
  • 62.7k
9 votes
Accepted

Never ending Query Store search

As I said in the answer, the empirical test showed that there were indexes on sys.plan_persisted* system objects without any (none) statistics created over them. I suspect that is because the database ...
Marian's user avatar
  • 15.5k
7 votes

dual table postgres doesn't return timestamp

Don't create dual tables Why are you using a dual table in PostgreSQL to begin with? PostgreSQL has an implicit dual table if there is no from-clause. SELECT now(); -- works fine. You can also ...
Evan Carroll's user avatar
  • 62.7k
7 votes

Why public is not mentioned as a fixed role in system database in SQL Server?

Public is just an odd duck. But to apply some retroactive continuity to the documentation you might say that all these built in roles are "fixed" or unchangeable in ways that normal roles are not. ...
David Browne - Microsoft's user avatar
7 votes
Accepted

How to find if a column is referenced in a computed column?

Thanks to Scott Hodgin I found it in sys.sql_expression_dependencies SELECT OBJECT_NAME(sed.referencing_id) AS referencingTable , pc.[name] AS computedColumn , pc.is_computed , ...
Zikato's user avatar
  • 4,403
7 votes
Accepted

What exactly triggers the UPDATE of the column is_media_read_only on sys.database_files?

I'm an engineer from SQL Server product team. This read-only media behavior is not by design and we issued a hotfix for it. To describe the behavior, the read-only media check is done when a file is ...
wonkim's user avatar
  • 86
7 votes
Accepted

How to decrypt the name of columns from view?

Looks like a user defined data type to me. By looking into sys.types and joining the columns system_type_id and further defining that system_type_id should be equal to user_type_id the query should ...
st100's user avatar
  • 344
6 votes

What is the persistent_version_store table?

Since this question hasn't had any comments or answers since August 2018 I'm going to assume nothing came of the large number of rows, but documenting the structure of the table might be useful to ...
Randolph West's user avatar
6 votes
Accepted

What's the values for the Freq_Interval column in MSDB.dbo.SysSchedules when Freq_Type is weekly and more than one day is selected in the schedule?

The documentation states freq_interval is one or more of the following: 1 = Sunday 2 = Monday 4 = Tuesday 8 = Wednesday 16 = Thursday 32 = Friday 64 = Saturday These values are all powers of 2....
Martin Smith's user avatar
  • 84.4k
6 votes

What exactly triggers the UPDATE of the column is_media_read_only on sys.database_files?

The value seems to get updated when a change is attempted on the file itself, such as LowlyDBA suggested. But only when the disk has been set to read only when no more modifications have to happen on ...
Randi Vertongen's user avatar
5 votes
Accepted

Update statistics on sys.objects

The warnings you're seeing most likely come from the sys.sysdepends view. If you script it out using EXEC sys.sp_helptext @objname = N'sys.sysdepends' The definition has a bunch of converts and ...
Erik Darling's user avatar
  • 40.5k
5 votes

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

There is a new system keyspace called "system_schema", but there are only two tables in it: Ok something must have gone wrong in your upgrade, because that's not right. When I check my ...
Aaron's user avatar
  • 4,055
5 votes
Accepted

Granting minimum select permission on system "sys" tables in every database

I can't seem to find a way around this other than creating a user for the login for every single database Starting with SQL Server 2014 the new CONNECT ANY DATABASE permission can be used to ...
sepupic's user avatar
  • 10.7k
5 votes
Accepted

How do I query INFORMATION_SCHEMA.COLUMNS on Amazon RDS?

PG_TABLE_DEF Amazon considers the internal functions that INFORMATION_SCHEMA.COLUMNS is using Leader-Node Only functions. Rather than being sensible and redefining the standardized INFORMATION_SCHEMA....
Evan Carroll's user avatar
  • 62.7k
5 votes

Never ending Query Store search

The underlying issue, which is visible if you open the actual plan in SSMS and look at at CPU usage (or examine XML), is node 32, a TVF. The culprit in slow Query Store queries is repeated access of ...
Forrest's user avatar
  • 4,139
5 votes
Accepted

MS SQL Server sys.indexes vs sys.sysindexes system table difference

sys.sysindexes - is old and deprecated. This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. ...
Kin Shah's user avatar
  • 62.2k
5 votes
Accepted

MS SQL Server differentiate system and user defined indexes using sys.indexes table

This is a way to select all the indexes of user defined tables select idx.[name] as [Index] from sys.indexes as idx inner join sys.objects as obj on idx.object_id = obj.object_id where idx.[name] ...
igelr's user avatar
  • 2,120
5 votes
Accepted

Why public is not mentioned as a fixed role in system database in SQL Server?

One reason that I can think of why public is NOT a fixed role - is_fixed_role is that the permissions can be changed. From BOL: The permissions that are granted to the fixed server roles (except ...
Kin Shah's user avatar
  • 62.2k
5 votes
Accepted

Why sys.columns.is_nullable can be null?

You are right that it is not possible for the outcome to ever be NULL. From quick investigation, take a look at the definition of sys.columns. If I run: SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys....
Aaron Bertrand's user avatar
5 votes
Accepted

T-SQL query to show table definition of sys.time_zone_info

sys.time_zone_info is a system view with the definition: CREATE VIEW sys.time_zone_info AS SELECT name, current_utc_offset, is_currently_dst FROM OPENROWSET(TABLE ...
Paul White's user avatar
  • 83.7k
5 votes
Accepted

sys.dm_exec_describe_first_result_set returns wrong data type

System types 106 and 108 are decimal and numeric, respectively. These type names are synonyms and can be used interchangeably: SELECT system_type_id, name FROM sys.types WHERE system_type_id IN(106, ...
Dan Guzman's user avatar
  • 28.1k
4 votes
Accepted

Where are SQL Server catalog views persisted?

Catalog views do not provide access to replication, SQL Server Agent, or backup metadata. Those are available in MSDB only. Reference: https://learn.microsoft.com/en-us/sql/relational-databases/...
SqlWorldWide's user avatar
  • 13.1k

Only top scored, non community-wiki answers of a minimum length are eligible