June 13, 2019 at 4:10 pm
Hi i want all column name in one row.
Here is example
table name - test
column name - name , code , status like i have 67 column
i want to have output like this
Select name , code , status all 67 columns from test table
i have like 50 tables with lots of columns
I donot want to do manually 1 by 1 select statement and get column name
is any way we can right from information_schema in one line for all columns
Appreciate any help
Thanks
June 13, 2019 at 4:19 pm
Just replace TestTable with your table name.
DECLARE @TABLE_NAME sysname
SET @TABLE_NAME = 'TestTable'
SELECT @TABLE_NAME,
STUFF((SELECT ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_SCHEMA = 'dbo'
AND C.TABLE_NAME = @TABLE_NAME
ORDER BY C.ORDINAL_POSITION
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'') x
Or this might be more suitable for your requirements:
SELECT 'SELECT ' +
STUFF((SELECT ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
ORDER BY C.ORDINAL_POSITION
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'') +
' FROM ' + QUOTENAME(T.TABLE_SCHEMA) + '.' + QUOTENAME(T.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES T
June 13, 2019 at 4:35 pm
When composing a query in SSMS, just drag from the 'Columns' node of the table you want (in Object Explorer) to SELECT from onto the editing window – SSMS will pull in all of the column names for you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 13, 2019 at 6:16 pm
Really appreciate works perfectly what i want. Thanks a tons...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply