Select all fields within database of certain datatype

Question

I am looking for an sql script which can select all fields in a database which are of a particualr datatype.

I have looked all over stack overflow and various pages i have found in google but yet no avail!

Maybe I am looking in the wrong places.

Any ideas?

Interesting one, my first question would have to be why are you trying to do this? Perhaps there is a better way.. — twoleggedhorse
– twoleggedhorse, Commented Jul 24, 2013 at 13:37
I have to manage 4 identical SQL servers which are all out of sync and I need to convert all datetime2 datatypes to datetime. I have the rest of the sql code which will do the conversion, the answer below is perfect. Feel a bit embarrassed its so simple... — Anton Hughes
– Anton Hughes, Commented Jul 24, 2013 at 13:48

Ashutosh Arya · Accepted Answer · 2013-07-24 13:39:04Z

3

This will give you list of all the fields to of perticular data type in a data base with table name may be you can work around this.

SELECT * FROM .INFORMATION_SCHEMA.COLUMNS where DATA_TYPE=''

Regards

Ashutosh Arya

answered Jul 24, 2013 at 13:39

Ashutosh Arya

1,1702 gold badges8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anton Hughes Over a year ago

Thank you! I thought it was going to be more complicated than that!

Ashutosh Arya Over a year ago

Welcome!You can find other tables unders information_Schema, if you wanna try something more. :)

Welshname · Accepted Answer · 2013-07-24 13:46:15Z

1

Try this

USE DatabaseName;
SELECT TABLE_CATALOG
    ,TABLE_SCHEMA
    ,TABLE_NAME
    ,COLUMN_NAME
    ,DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE LIKE 'varchar' --Or other Data Type

answered Jul 24, 2013 at 13:46

Welshname

231 silver badge6 bronze badges

Comments

thiagobraga · Accepted Answer · 2013-07-24 13:43:50Z

0

In MySQL, you can try select by type.

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE COLUMN_TYPE = 'datetime';

answered Jul 24, 2013 at 13:43

thiagobraga

1,5712 gold badges16 silver badges32 bronze badges

Collectives™ on Stack Overflow

Select all fields within database of certain datatype

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related