Question 90417 in PostgreSQL Question posted in
The official documentation can be found here.

How to select columns from list of tables – Postgresql

Let’s say I have a view in which I am selecting the names of tables from a particular schema. How can I use SELECT so that it display the columns and its values from these tables?

with tables_names as (
  select table_name 
  from information_schema. "columns" 
  where table_schema = 'xx'
)
select * from tables_names
Tags:

2

Answers


  1. 0

    If you want to display the column names in your query just add

    COLUMN_NAME

     with tables_names as (
  select table_name, column_name
  from information_schema. "columns" 
  where table_schema = 'xxx'
)
select * from tables_names
    Login or Signup to reply.
  2. 0

    Well, if your purpose is to list db-table column names:

    declare @tbl nvarchar(100) = 'person'
select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = @tbl

    Or more "extended" option:

    select table_schema, table_name, column_name
from information_schema.columns
where table_schema = 'dbo'

    "can I use SELECT so that it display the columns and its values from these tables". If you need to see table data based on table/column name(s):

    declare @tbl nvarchar(100) = 'person', @col nvarchar(100) = 'age'
exec('select * from ' + @tbl)
exec('select ' + @col + ' from ' + @tbl)

    Finally, if you want to find a specific value from all colums and all tables, you have to iterate over them using cursor (probably non-cursor option exists, do not see it yet…)

    declare @sch nvarchar(100), @tbl nvarchar(100), @col nvarchar(100) 

declare column_iterator cursor for
select table_schema, table_name, column_name
from information_schema.columns
where table_schema = 'dbo'

open column_iterator
fetch next from column_iterator into @sch, @tbl, @col
while @@FETCH_STATUS = 0
begin
    exec('select ' + @col + ' from ' + @sch + '.' + @tbl)
    -- here you're able to do whatever needed with column values
    fetch next from column_iterator into @sch, @tbl, @col
end
close column_iterator
deallocate column_iterator
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search