Comma Separated List of all columns in the database (class_name | Column_names ...)

Question

In SQL Server, I would like to see Table_Name and all the columns associated with this Table_Name in the database. So the output should look like this:

TABLE_NAME COLUMN_NAME 1. Employee Employee-id, Lastname, Firstname, Title........... 2. Orders Orderid, Order-date, shipped-date, delivery-date....... 3. Products Product-id, Product-name, supplier-id, category-id..... 4. Suppliers Supplier-id, Company-name, contact-name....... 5. ............................................................ 6. ................................................... (So on....) 

Is it possible to get the above results using WHILE LOOP or any other way? If YES, can you post the code.

In addition, I tried to make this problem using the Temp table:

  create table #hello (table_name1 Varchar(max)) insert into #hello(table_name1) select table_name from information_schema.columns GO create table #hello2 (table_name2 varchar(max),column_name2 varchar(max)) insert into #hello2(table_name2 ,column_name2) select table_name,column_name from information_schema.columns GO select a.table_name1,b.column_name from #hello a inner join information_schema.columns b on a.table_name1=b.table_name COLLATE Latin1_general_CI_AS order by table_name GO 

I was able to list the columns vertically, but I could not get a list of columns separated by commas.

Answer 1

 Select TABLE_SCHEMA, TABLE_NAME , Stuff( ( Select ', ' + C.COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS As C Where C.TABLE_SCHEMA = T.TABLE_SCHEMA And C.TABLE_NAME = T.TABLE_NAME Order By C.ORDINAL_POSITION For Xml Path('') ), 1, 2, '') As Columns From INFORMATION_SCHEMA.TABLES As T 

As mentioned in the comments, the above will include submissions. If you want to exclude views, you can do the following:

 Select T.TABLE_SCHEMA, T.TABLE_NAME , Stuff( ( Select ', ' + C.COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS As C Where C.TABLE_SCHEMA = T.TABLE_SCHEMA And C.TABLE_NAME = T.TABLE_NAME Order By C.ORDINAL_POSITION For Xml Path('') ), 1, 2, '') As Columns From INFORMATION_SCHEMA.TABLES As T Left Join INFORMATION_SCHEMA.VIEWS As V On V.TABLE_SCHEMA = T.TABLE_SCHEMA And V.TABLE_NAME = T.TABLE_NAME Where V.TABLE_NAME Is Null 

Answer 2

 select name as TABLE_NAME, STUFF(COLUMN_NAME, 1, 1, '') AS COLUMN_NAME from sys.tables t CROSS APPLY ( SELECT ',' + name AS [text()] FROM sys.columns c WHERE c.object_id = t.object_id FOR XML PATH('') ) o (COLUMN_NAME)