I use Microsoft SQL Server 2012. How to select all tables from a specific database in 1 line through a separator.

Suppose there is a TestDB database with the panels panel, settings, temp, log. You need to conduct a query whose answer will be "panel | settings | temp | log" In essence, this information is

SELECT TABLE_NAME FROM TestDB.INFORMATION_SCHEMA.Tables

Only here is how to convert this request to 1 line through the delimiter.

As I understand it, the group_concat function is not suitable for this task.

  • You are wrong about the group_concat account. It is, of course, not in MS SQL, but the Google query "MS SQL group_concat" gives a bunch of links on any of which there are examples of what you need - Mike
  • Please give at least 1 working example, I googled before asking a question here, and alas, I did not find anything worthwhile. - LorDo
  • We take the first link of Google: stackoverflow.com/questions/451415 from there we take the second answer, in which we replace column_name with table_name , a comma with a vertical line, the name of the table with INFORMATION_SCHEMA.Tables. Check - works. total: SELECT STUFF( (SELECT '|' + table_name FROM INFORMATION_SCHEMA.Tables FOR XML PATH ('')) , 1, 1, '') - Mike
  • This is what happens TABLE_NAME> | </ TABLE_NAME> < TABLE_NAME> | </ TABLE_NAME> <TABLE_NAME> | </ TABLE_NAME> <TABLE_NAME> | </ TABLE_NAME> <TABLE_NAME> | </ TABLE_NAME> <TABLE_NAME> | </ TABLE_NAME> <TABLE_NAME> | </ TABLE_NAME> <TABLE_NAME> | </ TABLE_NAME> <TABLE_NAME> | </ TABLE_NAME> <TABLE_NAME> | </ TABLE_NAME> <TABLE_NAME> | </ TABLE_NAME> <TABLE_TELTHATLE_TELTABLE_NAME_NAME> <TABLE_NAME> <TABLE_NAME> |
  • Then I can not imagine how to help you. I checked it in myself - it gave the names of the tables separated by a vertical bar - Mike

1 answer 1

Combining records into a string in sql-server is done by combining the result in XML. At the same time, when the column does not have a simple name (and the columns are collected by formulas, for example, using the plus operator, there is no name), ms-sql does not add tags to this XML and as a result we get a blank line. It remains to remove from the string the initial character | using STUFF() .

 SELECT STUFF( (SELECT '|' + table_name FROM INFORMATION_SCHEMA.Tables FOR XML PATH ('') ), 1, 1, '' )