Search All Columns of Specific Data Type in all Tables of Sql Server Database

Introduction: In this article I am going to share How to find all columns by specific data type in all tables and views in sql server database. 
Search All Columns of Specific Data Type in all Tables of Sql Server Database
In previous articles i explained How to Update table data using inner join in sql server and Drop or truncate parent table by dropping all foreign key constraints and Query to search any text in all stored procedures, views and functions and Query to find all foreign keys references of particular table and CTE recursive query to get employee manager hierarchy with level

Description:  While working on project it was required to change the data type of all decimal columns to numeric in all tables of the database. There were more than 500 tables in our database so it was time consuming process to check all tables to look for decimal columns. So I searched internet for some hack and got easy solution. We can search all columns by any specific data types using the query mentioned below:

Implementation: Let’s write the query to get list of tables having columns of decimal data type
Query to search particular column in all tables by data type

SELECT
TABLE_SCHEMA AS [Schema],
TABLE_NAME AS [Table/View],
COLUMN_NAME AS [Column],
DATA_TYPE     AS [DataType]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'decimal'

Result:
Schema
Table/View
Column
DataType
dbo
tbBookMaster
BookPrice
Decimal
dbo
vwBookDetails
BookPrice
Decimal
dbo
tbBookDetails
Price
Decimal
So I got the list of the tables where there were columns of decimal data type. Now it was easy for me to change data type of the columns in only these tables out of the 500 tables.

Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..