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'


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

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: FacebookPinterestTITwitter
本文标题:Search All Columns of Specific Data Type in all Tables of Sql Server Database

技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(17)