Tiffany Thang <tiffanythang@xxxxxxxxx> writes: > Does anyone have a query that will list all the objects (composite types, > sequences, tables, triggers, functions, indices, etc) owned by a schema or > owner? I find fragments of information here and there that query on > pg_tables, pg_views, etc. Well, you could attack it the hard way: select relname from pg_class where relowner = [oid of role of interest] union all ... similar select from every other catalog that has an owner column ... I don't know if anyone's built such a query before, but a little quality time with the system catalog documentation would get you there: https://www.postgresql.org/docs/current/static/catalogs.html Another idea is to rely on owner dependencies recorded in pg_shdepend, along the lines of select pg_describe_object(classid,objid,objsubid) from pg_shdepend where deptype = 'o' and refobjid = [oid of role of interest] and dbid = [oid of current database]; That won't work for objects owned by the bootstrap superuser, and I think there are some other omissions --- for instance, it looks like we only record an owner dependency for a table, not for its indexes. Or, if you're feeling truly lazy, you can do begin; drop user joe; -- read the error message whining about what joe owns rollback; That's largely a hacky way to get the same info as the pg_shdepend query I sketched before, since the error message is derived from exactly that info. You have the same three options for schemas, though the details of each are a bit different (in particular, schema dependencies would be found in pg_depend not pg_shdepend). regards, tom lane
