Get columns name
-
So, you are saying that I need to do a static analyze on SELECT text and see what columns I have, if I enumerate the columns in the SELECT statement, then I have it, if I don't (I have '*') then I have to query the tables names / columns name and will find them. I thought there is a SQL trick to find the columns name in any circumstances.
I'd implement such a "trick" in a stored procedure that gets a query as an argument, then creates the temporary View, uses
SELECT [TABLE_NAME]
,[COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS]
where TABLE_NAME =to return the recordset with the tables/columns names, then delete the temp view. PS: this should work with SQL Server 2008 and above.
-
I'd implement such a "trick" in a stored procedure that gets a query as an argument, then creates the temporary View, uses
SELECT [TABLE_NAME]
,[COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS]
where TABLE_NAME =to return the recordset with the tables/columns names, then delete the temp view. PS: this should work with SQL Server 2008 and above.
-
Sound good ! Because this solution should be cross server platform, it is available on the other SQL servers, like Oracle, Informix, MySQL, and so on ?
You have to test it yourself! Just check out the links I gave you [here](https://www.codeproject.com/Messages/5808300/Re-Get-columns-name)
-
If I run a SQL statement, like this:
SELECT * FROM my_table
is there any SQL statement / trick to find the name of every column from '*' ? Because if I write:
SELECT id, name, age FROM my_table
yes, I could extract the columns name, by text analyze. But how about '*' case ?
If you load your query into a DataTable, you can get the column names from meta data. [datatable - How do I get column names to print in this C# program? - Stack Overflow](https://stackoverflow.com/questions/2557937/how-do-i-get-column-names-to-print-in-this-c-sharp-program)
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food
-
If you load your query into a DataTable, you can get the column names from meta data. [datatable - How do I get column names to print in this C# program? - Stack Overflow](https://stackoverflow.com/questions/2557937/how-do-i-get-column-names-to-print-in-this-c-sharp-program)
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food
AFAIK, the OP develops in C++. However, I may be wrong.
-
AFAIK, the OP develops in C++. However, I may be wrong.
[Data Access Using ADO.NET (C++/CLI) | Microsoft Docs](https://docs.microsoft.com/en-us/cpp/dotnet/data-access-using-adonet-cpp-cli?view=msvc-160)
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food
-
[Data Access Using ADO.NET (C++/CLI) | Microsoft Docs](https://docs.microsoft.com/en-us/cpp/dotnet/data-access-using-adonet-cpp-cli?view=msvc-160)
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food
But it is managed C++/CLI. :sigh: It has nothing to do with the native C++. :-O
-
But it is managed C++/CLI. :sigh: It has nothing to do with the native C++. :-O
Yeah, but ... [Using ADO.NET in MFC Projects](https://www.codeproject.com/Articles/4735/Using-ADO-NET-in-MFC-Projects) (And it's a "database" forum) Before ADO.NET there was ADO, and MFC did ADO.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food
-
Yeah, but ... [Using ADO.NET in MFC Projects](https://www.codeproject.com/Articles/4735/Using-ADO-NET-in-MFC-Projects) (And it's a "database" forum) Before ADO.NET there was ADO, and MFC did ADO.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food
Interesting idea. Thank you! BTW, I used ADO in my big VS2010 project (C++/MFC with ADO with SQL Server) from 2009 to 2015, of course without any mixture with managed code! ;)
-
If I run a SQL statement, like this:
SELECT * FROM my_table
is there any SQL statement / trick to find the name of every column from '*' ? Because if I write:
SELECT id, name, age FROM my_table
yes, I could extract the columns name, by text analyze. But how about '*' case ?
Try this
select * from table1 where 1=1this should return an empty datatable with all the columns. Simply iterate the columns to get their names.Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
If I run a SQL statement, like this:
SELECT * FROM my_table
is there any SQL statement / trick to find the name of every column from '*' ? Because if I write:
SELECT id, name, age FROM my_table
yes, I could extract the columns name, by text analyze. But how about '*' case ?
-
Try: SELECT [name]. [column_id] AS [Ordinal] FROM sys.columns WHERE [object_id] = OBJECT_ID('MyTable') ORDER BY [column_id]; That will get the column names and ordinal position.
I suggest you read the documentation:
sys.columns (Transact-SQL) - SQL Server | Microsoft Docs[^]:
Column IDs might not be sequential.
If a column has ever been dropped from the table, you will end up with gaps in the column ID sequence. To get a true ordinal position, you'd need to use the
ROW_NUMBERwindowing function - for example:SELECT [name], ROW_NUMBER() OVER (ORDER BY [column_id]) As [Ordinal]
FROM sys.columns
WHERE [object_id] = OBJECT_ID('MyTable')
ORDER BY [column_id]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
