Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# Examples of catalog queries
The following queries show a few of the ways in which you can query the catalog tables to get useful information about an Amazon Redshift database.
## View table ID, database, schema, and table name
The following view definition joins the STV\$1TBL\$1PERM system table with the PG\$1CLASS, PG\$1NAMESPACE, and PG\$1DATABASE system catalog tables to return the table ID, database name, schema name, and table name.
```
create view tables_vw as
select distinct(stv_tbl_perm.id) table_id
,trim(pg_database.datname) db_name
,trim(pg_namespace.nspname) schema_name
,trim(pg_class.relname) table_name
from stv_tbl_perm
join pg_class on pg_class.oid = stv_tbl_perm.id
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
join pg_database on pg_database.oid = stv_tbl_perm.db_id;
```
The following example returns the information for table ID 117855.
```
select * from tables_vw where table_id = 117855;
```
```
table_id | db_name | schema_name | table_name
---------+-----------+-------------+-----------
117855 | dev | public | customer
```
## List the number of columns per Amazon Redshift table
The following query joins some catalog tables to find out how many columns each Amazon Redshift table contains. Amazon Redshift table names are stored in both PG\$1TABLES and STV\$1TBL\$1PERM; where possible, use PG\$1TABLES to return Amazon Redshift table names.
This query does not involve any Amazon Redshift tables.
```
select nspname, relname, max(attnum) as num_cols
from pg_attribute a, pg_namespace n, pg_class c
where n.oid = c.relnamespace and a.attrelid = c.oid
and c.relname not like '%pkey'
and n.nspname not like 'pg%'
and n.nspname not like 'information%'
group by 1, 2
order by 1, 2;
nspname | relname | num_cols
--------+----------+----------
public | category | 4
public | date | 8
public | event | 6
public | listing | 8
public | sales | 10
public | users | 18
public | venue | 5
(7 rows)
```
## List the schemas and tables in a database
The following query joins STV\$1TBL\$1PERM to some PG tables to return a list of tables in the TICKIT database and their schema names (NSPNAME column). The query also returns the total number of rows in each table. (This query is helpful when multiple schemas in your system have the same table names.)
```
select datname, nspname, relname, sum(rows) as rows
from pg_class, pg_namespace, pg_database, stv_tbl_perm
where pg_namespace.oid = relnamespace
and pg_class.oid = stv_tbl_perm.id
and pg_database.oid = stv_tbl_perm.db_id
and datname ='tickit'
group by datname, nspname, relname
order by datname, nspname, relname;
datname | nspname | relname | rows
--------+---------+----------+--------
tickit | public | category | 11
tickit | public | date | 365
tickit | public | event | 8798
tickit | public | listing | 192497
tickit | public | sales | 172456
tickit | public | users | 49990
tickit | public | venue | 202
(7 rows)
```
## List table IDs, data types, column names, and table names
The following query lists some information about each user table and its columns: the table ID, the table name, its column names, and the data type of each column:
```
select distinct attrelid, rtrim(name), attname, typname
from pg_attribute a, pg_type t, stv_tbl_perm p
where t.oid=a.atttypid and a.attrelid=p.id
and a.attrelid between 100100 and 110000
and typname not in('oid','xid','tid','cid')
order by a.attrelid asc, typname, attname;
attrelid | rtrim | attname | typname
---------+----------+----------------+-----------
100133 | users | likebroadway | bool
100133 | users | likeclassical | bool
100133 | users | likeconcerts | bool
...
100137 | venue | venuestate | bpchar
100137 | venue | venueid | int2
100137 | venue | venueseats | int4
100137 | venue | venuecity | varchar
...
```
## Count the number of data blocks for each column in a table
The following query joins the STV\$1BLOCKLIST table to PG\$1CLASS to return storage information for the columns in the SALES table.
```
select col, count(*)
from stv_blocklist s, pg_class p
where s.tbl=p.oid and relname='sales'
group by col
order by col;
col | count
----+-------
0 | 4
1 | 4
2 | 4
3 | 4
4 | 4
5 | 4
6 | 4
7 | 4
8 | 4
9 | 8
10 | 4
12 | 4
13 | 8
(13 rows)
```