find data type of a field in a select
-
Is there a function that would return the data type of a field?
For example:
select get_datatype(myfield) from my table
-
Just turn this into a sproc, and use in the desired DB. This will work in Northwind
USE Northwind
GO
DECLARE @Table varchar(35),
@Column varchar(35)
SELECT @Table = 'Employees',
@Column = 'EmployeeID'
SELECT type_name(st.xtype)
FROM systypes st
JOIN syscolumns sc
ON st.xtype = sc.xtype
JOIN sysobjects so
ON sc.id = so.id
WHERE so.name = @Table
AND sc.name = @Column
Although - just using sp_help [Table] would be faster even though it gives you more info than you may want.
-
Thanks for the tip, but not exactly what I was looking for.
What about xp's? Or If I say select @@servername? I know that is a nvarchar...but is there an easy way to find this aside from docs?
I'm wondering how 2005 does it in SSIS. It is able to alert you to problems with your data types during design time.
-
What do you mean by xp'x and @@severname? I thought you were looking for data types of specific columns. What is "docs?" ????
-
Not exactly - a little more general. I said a field in a select statement, not a column in a table - I guess I could have said 'item'. Another example of what I'm looking for.
Select get_dataType(getdate()) as my_dataType;
my_dataType
------------------
DateTime
>>What do you mean by xp'x and @@severname? I thought you were looking for data types of specific columns. What is "docs?" ????
Try "Select @@servername" - it's a global variable. Docs are documentation.
-
Based on what I have read, I think the answer to your question about there being a function that will return the datatype of an input; a table column in a select statement, or a system variable, etc., is no.
-
No, pretty sure there's nothing built in... you'd have to do a SELECT INTO a temp table and then read the datatype from the columns something like this...
IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
DROP TABLE #MyHead
SELECT TOP 1
GETDATE() AS ColA,
NEWID() AS ColB,
@@SERVERNAME AS ColC,
au.*
INTO #MyHead
FROM Pubs.dbo.Authors au --Just an example table...
SELECT sc.Name,TYPE_NAME(sc.XType)
FROM TempDB.dbo.SysColumns sc
WHERE sc.ID = OBJECT_ID('TempDB.dbo.#MyHead')
But, normally you know what the datatype of "fields" in the SELECT list are at design time... so, I'm curious... why would you need such a function? In other words, what are you trying to do (other than the obvious)?
Someone might already have it 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
this is one of those situations where the client side would have more functionality than server side.
once placed in an ADODB recordset, the recordset has the datatype and defined size for every field.
data table would have similar information, but when looking at it in query analyzer, it's not obvious, but it's there behind the scenes.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I just thought it might come in handy, day to day, to get a quick return type instead of jumping to the manual. There is always some little trick I don't know - thought this might be one. Thanks for your help guys!:D
-
I understand your point of view... a shortcut like this could be usefull. But can you give me a single production context where this comes in handy??? I'm sure there are a few exceptions... but I've never encountered them.
-
Ya know... I actually get this one... there's been a couple of times that I've had to lookup the datatype for SysName and @@ServerName and the like just because I forget or want to make double sure...
But, it's easy to do... I have a couple of "Favorites" selections in Books Online... DataTypes and System Functions are a couple of those...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
While it doesn't help for this problem, it can certainly help in a similar situation : COLUMNPROPERTY() has a lot of usefull info.
-
Which property of COLUMN_PROPERTY do you ask for to determine the datatype of a column and how do you use it on a system function like @@ServerName?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Honestly I don't understand how much faster do you get when BOL has the "return type" and is very easy to bookmark/use 😉
* Noel -
As I said, while IT DOESN'T help for this case... that function has a lot of good info...
:).
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply