Return all tables where specific enum is used
I am trying to return all tables where a specific enum is used. So far I have this:
select *
from pg_type t
join pg_enum e on t.oid = e.enumtypid
join pg_catalog.pg_namespace n
ON n.oid = t.typnamespace and t.typname = 'enum';
How could I modify this query to return the actual tables, or is there something altogether different that is better to use?
sql postgresql
edited Nov 12 at 19:12
Laurenz Albe
44.2k102746
asked Nov 12 at 18:50
Anton Kim
330213
add a comment |
I am trying to return all tables where a specific enum is used. So far I have this:
select *
from pg_type t
join pg_enum e on t.oid = e.enumtypid
join pg_catalog.pg_namespace n
ON n.oid = t.typnamespace and t.typname = 'enum';
How could I modify this query to return the actual tables, or is there something altogether different that is better to use?
sql postgresql
edited Nov 12 at 19:12
Laurenz Albe
44.2k102746
asked Nov 12 at 18:50
Anton Kim
330213
add a comment |
I am trying to return all tables where a specific enum is used. So far I have this:
select *
from pg_type t
join pg_enum e on t.oid = e.enumtypid
join pg_catalog.pg_namespace n
ON n.oid = t.typnamespace and t.typname = 'enum';
How could I modify this query to return the actual tables, or is there something altogether different that is better to use?
sql postgresql
edited Nov 12 at 19:12
Laurenz Albe
44.2k102746
asked Nov 12 at 18:50
Anton Kim
330213
I am trying to return all tables where a specific enum is used. So far I have this:
select *
from pg_type t
join pg_enum e on t.oid = e.enumtypid
join pg_catalog.pg_namespace n
ON n.oid = t.typnamespace and t.typname = 'enum';
How could I modify this query to return the actual tables, or is there something altogether different that is better to use?
sql postgresql
sql postgresql
edited Nov 12 at 19:12
Laurenz Albe
44.2k102746
asked Nov 12 at 18:50
Anton Kim
330213
edited Nov 12 at 19:12
Laurenz Albe
44.2k102746
asked Nov 12 at 18:50
Anton Kim
330213
edited Nov 12 at 19:12
Laurenz Albe
44.2k102746
edited Nov 12 at 19:12
Laurenz Albe
44.2k102746
edited Nov 12 at 19:12
Laurenz Albe
44.2k102746
44.2k102746
asked Nov 12 at 18:50
Anton Kim
330213
asked Nov 12 at 18:50
Anton Kim
330213
asked Nov 12 at 18:50
Anton Kim
330213
330213
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
The simplest query would be, given that the type you look for is typschema.typname:
SELECT t.relnamespace::regnamespace AS schema,
t.relname AS tablename
FROM pg_catalog.pg_class AS t
JOIN pg_catalog.pg_attribute AS c
ON t.oid = c.attrelid
WHERE c.atttypid = 'typschema.typname'::regtype;
answered Nov 12 at 19:10
Laurenz Albe
44.2k102746
add a comment |
select * from information_schema.columns
where table_schema not in ('information_schema','pg_catalog')
and data_type='enum'
answered Nov 12 at 19:33
DSKalugin
111
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53268347%2freturn-all-tables-where-specific-enum-is-used%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The simplest query would be, given that the type you look for is typschema.typname:
SELECT t.relnamespace::regnamespace AS schema,
t.relname AS tablename
FROM pg_catalog.pg_class AS t
JOIN pg_catalog.pg_attribute AS c
ON t.oid = c.attrelid
WHERE c.atttypid = 'typschema.typname'::regtype;
answered Nov 12 at 19:10
Laurenz Albe
44.2k102746
add a comment |
The simplest query would be, given that the type you look for is typschema.typname:
SELECT t.relnamespace::regnamespace AS schema,
t.relname AS tablename
FROM pg_catalog.pg_class AS t
JOIN pg_catalog.pg_attribute AS c
ON t.oid = c.attrelid
WHERE c.atttypid = 'typschema.typname'::regtype;
answered Nov 12 at 19:10
Laurenz Albe
44.2k102746
add a comment |
The simplest query would be, given that the type you look for is typschema.typname:
SELECT t.relnamespace::regnamespace AS schema,
t.relname AS tablename
FROM pg_catalog.pg_class AS t
JOIN pg_catalog.pg_attribute AS c
ON t.oid = c.attrelid
WHERE c.atttypid = 'typschema.typname'::regtype;
answered Nov 12 at 19:10
Laurenz Albe
44.2k102746
The simplest query would be, given that the type you look for is typschema.typname:
SELECT t.relnamespace::regnamespace AS schema,
t.relname AS tablename
FROM pg_catalog.pg_class AS t
JOIN pg_catalog.pg_attribute AS c
ON t.oid = c.attrelid
WHERE c.atttypid = 'typschema.typname'::regtype;
answered Nov 12 at 19:10
Laurenz Albe
44.2k102746
answered Nov 12 at 19:10
Laurenz Albe
44.2k102746
answered Nov 12 at 19:10
Laurenz Albe
44.2k102746
answered Nov 12 at 19:10
Laurenz Albe
44.2k102746
44.2k102746
add a comment |
add a comment |
select * from information_schema.columns
where table_schema not in ('information_schema','pg_catalog')
and data_type='enum'
answered Nov 12 at 19:33
DSKalugin
111
add a comment |
select * from information_schema.columns
where table_schema not in ('information_schema','pg_catalog')
and data_type='enum'
answered Nov 12 at 19:33
DSKalugin
111
add a comment |
select * from information_schema.columns
where table_schema not in ('information_schema','pg_catalog')
and data_type='enum'
answered Nov 12 at 19:33
DSKalugin
111
select * from information_schema.columns
where table_schema not in ('information_schema','pg_catalog')
and data_type='enum'
answered Nov 12 at 19:33
DSKalugin
111
answered Nov 12 at 19:33
DSKalugin
111
answered Nov 12 at 19:33
DSKalugin
111
answered Nov 12 at 19:33
DSKalugin
111
111
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53268347%2freturn-all-tables-where-specific-enum-is-used%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
