-3

I would like to get all tables and their columns in one table.

For example:

Table 1 | Column1 | Column2 | Column3 | 

Table 2 | Column1 |

Table 3 | Column1 | Column2 | Column3 | Column4 | Column5

Table 4 | Column1 | Column2 |

Table 5 | Column1 | Column2 | Column3 | Column4 | Column5

Table 6 | Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7

EDIT:

Version I'm using: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)

Falcon xoxo
  • 1
  • 1
  • 2
    check https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database – StackTrace Sep 10 '18 at 11:34
  • @Larnu Not entire true, he wants it all to be in one table. Not to select all via information schema..As far as i can read. – SqlKindaGuy Sep 10 '18 at 11:37
  • @Thomas that's unclear from their post. Especially as, if it is one table, their expected output has a variable amount of columns per row (an impossibility in SQL Server). – Thom A Sep 10 '18 at 11:45

2 Answers2

0

EDIT

As jarlh points out - You need to be sure that the datatypes for each column is of same type otherwise you cannot union them.

So if col1 is normally an Integer, you cannot have col1 with string values. Unless you convert it to string.

If it should just be static do like this:

Select Col1,Col2,Col3 from Table1
union all
Select col1,null as Col2, null as Col3 from table2

And so on.

So if you largest table has 7 columns it should be

Select col1, col2,col3,null as col4,null as col5,null as col6,null as col7 from table1
union all
select col1, null as col2, null as col3.................. ,null as col7 from table2
SqlKindaGuy
  • 3,501
  • 2
  • 12
  • 29
0

Use below query .

SELECT t.name as TableName, 
stuff(
        (select '|'+ CAST(c.name as varchar(max)) from sys.columns c
    where c.object_id = t.object_id for xml path('')),1,1,'') as columnlist
from sys.tables t
NP007
  • 659
  • 8
  • 21