SQL to get DataTypes
-
Hi Is it possible to write a query that will return the datatype of each field as opposed to actual data? tks Richard
Look at the INFORMATION_SCHEMA.COLUMNS to find the data type of columns in the database. (Assums SQL Server - You didn't say, but it is the most common database used on this forum)
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
Hi Is it possible to write a query that will return the datatype of each field as opposed to actual data? tks Richard
I use ExecuteReader and then ask the DataReader for the DataTypes. DataReader.GetSchemaTable() or DataReader.GetFieldType()
-
Hi Is it possible to write a query that will return the datatype of each field as opposed to actual data? tks Richard
RichardBerry wrote:
Is it possible to write a query that will return the datatype of each field
In sql Server, there is a system stored procedure (
sp_columns) which returns column information for the specified table or view. You can execute that to get the DataType of the columns.Exec sp_columns [@TableName]Regards
J O H N :rose:
"Even eagles need a push." David McNally
-
Look at the INFORMATION_SCHEMA.COLUMNS to find the data type of columns in the database. (Assums SQL Server - You didn't say, but it is the most common database used on this forum)
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
Hi Colin Yes Using SQL server, but how do I do this? I looked, but don't seem to find a table with this name (INFORMATION_SCHEMA.COLUMNS)?
-
RichardBerry wrote:
Is it possible to write a query that will return the datatype of each field
In sql Server, there is a system stored procedure (
sp_columns) which returns column information for the specified table or view. You can execute that to get the DataType of the columns.Exec sp_columns [@TableName]Regards
J O H N :rose:
"Even eagles need a push." David McNally
Hi John Thanks for your reply. I tried running that stored procedure from MS Query, and get the following fields returned,but with no data. TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE..... Etc It would actually be great if I could get all table data for the database in the above format. When executing 'Exec sp_columns [@TableName]' I tried replacing 'TableName' with various strings but cant seem to get any data back. I tried *, vektron.scheme.units, scheme.units, units. But none seemed to work. Typically to execute a normal Select query, I would use: SELECT * FROM vektron.scheme.units Any idea what I could be doing wrong?
-
Hi John Thanks for your reply. I tried running that stored procedure from MS Query, and get the following fields returned,but with no data. TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE..... Etc It would actually be great if I could get all table data for the database in the above format. When executing 'Exec sp_columns [@TableName]' I tried replacing 'TableName' with various strings but cant seem to get any data back. I tried *, vektron.scheme.units, scheme.units, units. But none seemed to work. Typically to execute a normal Select query, I would use: SELECT * FROM vektron.scheme.units Any idea what I could be doing wrong?
RichardBerry wrote:
I tried running that stored procedure from MS Query, and get the following fields returned,but with no data.
return's no data? How come? It works fine in my system... I have a table with the name tblProducts and I executed the stored proc as below...It return's the complete information...
Exec sp_columns [tblProducts]Regards
J O H N :rose:
"Even eagles need a push." David McNally
-
RichardBerry wrote:
I tried running that stored procedure from MS Query, and get the following fields returned,but with no data.
return's no data? How come? It works fine in my system... I have a table with the name tblProducts and I executed the stored proc as below...It return's the complete information...
Exec sp_columns [tblProducts]Regards
J O H N :rose:
"Even eagles need a push." David McNally
Hi John Got it right - my syntax was wrong - I was putting in the '@' before the table name. BTW, is there a way to get more the info on all tables at once? I tried Exec sp_columns [*] but that gave no data back? Thanks for your help!!
-
Hi John Got it right - my syntax was wrong - I was putting in the '@' before the table name. BTW, is there a way to get more the info on all tables at once? I tried Exec sp_columns [*] but that gave no data back? Thanks for your help!!
-
Hi Colin Yes Using SQL server, but how do I do this? I looked, but don't seem to find a table with this name (INFORMATION_SCHEMA.COLUMNS)?
RichardBerry wrote:
Yes Using SQL server, but how do I do this? I looked, but don't seem to find a table with this name (INFORMATION_SCHEMA.COLUMNS)?
It is a view, not a table. It is on the Master database and will be picked up regardless of which database you are in. I'm also curious, did it not occur to you just to type
SELECT * FROM INFORMATION_SCHEMA.COLUMNSor search for references to it on the internet?
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
RichardBerry wrote:
Yes Using SQL server, but how do I do this? I looked, but don't seem to find a table with this name (INFORMATION_SCHEMA.COLUMNS)?
It is a view, not a table. It is on the Master database and will be picked up regardless of which database you are in. I'm also curious, did it not occur to you just to type
SELECT * FROM INFORMATION_SCHEMA.COLUMNSor search for references to it on the internet?
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
Hi Colin Thanks that was exactly what I was looking for.
Colin Angus Mackay wrote:
I'm also curious, did it not occur to you just to type SELECT * FROM INFORMATION_SCHEMA.COLUMNS or search for references to it on the internet?
No it did not occur to me, since I looked through all the databases on the server, and could not find a table with that name, so I thought perhaps this was a table that was not in my database. I had never heard of a 'view' before, so I am currently GOOGLING 'SQL View' to learn more about that. It is sometimes difficult for a NOVICE (myself) to ask the right questions or look in the right places for information.