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.
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.
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..