How to select columns from list of tables-CodePudding

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

CodePudding user response：

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 = 'bison_stg'
)
select * from tables_names

CodePudding user response：

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