I found following T-SQL query to build a string of selected column names together separated by command for a table. This string can be used then for another SQL query as a column list.
The first thing what I did is to use Coaslesce function to combine a list of row values into a string, separated by ',' if a value is not null or '' if null.
The second trick is to use information_schema.colums to get column names by column_name. Notice that table_name and column_name are a special key words here for table and column names.
1 DECLARE @v_FiledList NVARCHAR(MAX);
2 -- Use Coalesce to combine rows to a string
3 SELECT @v_FieldList = COALESCE(@v_FieldList + ',','') + column_name
4 FROM (
5 -- Use information_schema to get table's column names
6 SELECT column_name FROM information_schema.columns
7 WHERE table_name = 'myTable' AND
8 column_name NOT LIKE 'keyCol' -- filter col 'keyCol' out
9 ) AS A;
10 PRINT 'Field list: ' + @v_FieldList;
You can get more detail information such as data type, length, and so on from information_schema.columns:
SELECT column_name, *
FROM information_schema.columns
WHERE table_name = 'myTable'
Posts
1 comments:
--CODE SNIPPET TO LIST TABLE COLUMNS
-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE
--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'
--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'
--Declare the Table variable
DECLARE @ColNames TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
ColName VARCHAR(300) --The string value
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
--Populate the TABLE variable using some logic
INSERT INTO @ColNames SELECT column_name from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Feature'
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @ColNames
--A variable to hold the currently selected value from the table
DECLARE @CurrentValue varchar(300);
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count
BEGIN
--Load current value from the Table
SELECT @CurrentValue = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--Process the current value
if @CurrentDelimiterPositionVar = @Count
print '[' + @CurrentValue + ']' -- this is the last row no comma!
else
print '[' + @CurrentValue + '],' -- print it without the ,
-- print 'obj1.' + @CurrentValue+ ' = obj2.'+ @CurrentValue
-- print @CurrentValue --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END
set nocount off
Post a Comment