Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [catalogs]

53 questions
487
votes
5 answers

List all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with administrator rights. It's a third-party product, and…
Stephane Rolland
  • 7,903
  • 8
  • 29
  • 40
32
votes
5 answers

Get column names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should return a list like: Column Name | Column…
vfclists
  • 947
  • 3
  • 12
  • 20
32
votes
7 answers

Display user-defined types and their details

I've created a few new UDTs in PostgreSQL. However, now I have two problems: how to see which UDTs have been defined? how to see the columns defined within these UDTs? Unfortunately, I couldn't find anything on that in the PostgreSQL…
navige
  • 559
  • 1
  • 5
  • 8
15
votes
2 answers

Check if postgresql database exists (case insensitive way)

Is there a "elegant built-in" case-insensitive way to check if db is exists? I've found only SELECT datname FROM pg_catalog.pg_database WHERE datname='dbname', but this is a CS check. The first thing that comes to mind to retrieve all db names and…
Andrei Orlov
  • 447
  • 2
  • 4
  • 12
12
votes
4 answers

How can I determine if a table exists in the current search_path with PLPGSQL?

I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist. If not, I want to give the user a useful error. However, I don't know what schema will be…
cimmanon
  • 315
  • 1
  • 3
  • 14
11
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,054
  • 2
  • 10
  • 23
9
votes
8 answers

Permission to view execution report in SSIS Catalog

We are currently using SSIS 2012. Is there any way for a user to view execution reports under SSIS Catalog without being ssis_admin or sysadmin? This is for production environment and we don't want people to manipulate SSIS Catalog projects. Thanks!
Joann.B
  • 291
  • 1
  • 4
  • 16
9
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,105
  • 8
  • 37
  • 60
7
votes
1 answer

Inner join on array column in Postgres

In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres. It looks like the names of such columns are contained in pg_constraint. According to the docs, the column relevant…
s.m.
  • 319
  • 1
  • 4
  • 10
7
votes
3 answers

Restore All Databases Script

I am migrating SQL Server DBs to a new instance. I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog. Does anyone know of a sample script to do this? Thanks!
K09
  • 1,372
  • 11
  • 36
  • 58
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
  • 59,330
  • 43
  • 219
  • 447
6
votes
1 answer

What is the significance of the principal_id in sys.schemas?

What is the meaning of the principal_id in sys.schemas and when would it ever be different from schema_id? 1> SELECT LEFT(name,20), schema_id, principal_id FROM sys.schemas; 2> GO schema_id principal_id --------------------…
Evan Carroll
  • 59,330
  • 43
  • 219
  • 447
6
votes
4 answers

Oracle data dictionary, tell built-in roles apart from custom roles

In Oracle, if you describe the DBA_ROLES view, you only get three columns: SQL> describe dba_roles; Name Null Type ------------------------------------------------ ROLE NOT NULL VARCHAR2(30) …
Tulains Córdova
  • 703
  • 6
  • 13
  • 24
5
votes
2 answers

Dropping a group of schemas with similar name patterns

Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object. In my case, I need to drop some schemas, all of the form ceu_shard_test_merge_*, to use shell globbing…
Faheem Mitha
  • 989
  • 2
  • 11
  • 18
5
votes
2 answers

ERROR: could not find array type for datatype information_schema.sql_identifier

I am trying to run the below sql command: SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage' ); and I get the below error: ERROR: could not find array type for datatype…
John Mitchell
  • 195
  • 1
  • 2
  • 5
1
2 3 4