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 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
分页:12
转载请注明
本文标题:Search All Columns of Specific Data Type in all Tables of Sql Server Database
本站链接:http://www.codesec.net/view/482554.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(34)