Is there an sql way to determine field type? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is there an sql way to determine field type?

Author

Topic

krausr79
Starting Member

19 Posts

Posted - 2012-08-24 : 10:54:13

I'm writing an excel macro to change things in any table inputted. I don't know ahead of time which fields are what data type. When something in a numeric field is changed, it will be entered ,1234, and when something in a alphanumeric field is changed, it will be entered ,'1234', . Some of these part numbers have letters in them, and sometimes the query will be small enough that only numeric part numbers show up, so it won't be airtight to check whether a field (or all entries in that field) are numeric because it may still be an alphanumeric field.

Is there an sql way to get field data types?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-24 : 10:56:33

You can use INFORMATION_SCHEMA.columns, like this:

SELECT
	data_type
FROM
	INFORMATION_SCHEMA.columns
WHERE
	TABLE_NAME = 'YourTableName'
	AND COLUMN_NAME = 'YourColumnName'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 10:58:09

you mean after creating the table or just scan values and determine data type?

look for SQL_VARIANT_PROPERTY() function

http://msdn.microsoft.com/en-us/library/ms178550.aspx

and sql_variant datatype

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

krausr79
Starting Member

19 Posts

Posted - 2012-08-24 : 11:21:46

Thanks much. I'm not using transact, so I'll go with Sunita's method.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 11:28:10

quote:
Originally posted by krausr79

Thanks much. I'm not using transact, so I'll go with Sunita's method.

hmm...its still a transact sql query isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources