BISQL # 91 – How to alter Similar column Or Alter All column in one Query in Entire Database , Fast and Furious !
Hi Friends,
Now onwards we will have most of our post on development plane of SQL server 2012
In this article we are going to cover How to find similar column name in entire Database.
and most important and focused will be on how to Alter that Column in entire DB .
Lets start with example with adventure works DB
Now here is Problem Statement : I want to make column name as nvarchar (60) instead of nvarchar (50) through out the database.
Following query Finds out all column in table with name
SELECT s.name + '.'+t.name ,C.name ,c.user_type_id from sys.tables t Inner join sys.schemas s on t.schema_id = s.schema_id inner join sys.columns c on t.object_id = c.object_id WHERE c.name Like 'name' AND user_type_id = '260'
Output of above query will be
Following is Query analysis for above result with some explanation
Now i got where i have apply alter script
Following is query which i have for generating alter script for column name – ‘Name’
Select 'Alter Table '+s.name + '.'+t.name +' Alter Column ' +C.name + ' nvarchar(60)' from sys.tables t Inner join sys.schemas s on t.schema_id = s.schema_id inner join sys.columns c on t.object_id = c.object_id Where c.name Like 'name' and c.default_object_id = 0
Output of query is as simple shown :
Query analysis as follows :
After this just copy n paste result and execute all we will get desired result
Hope you will like this post on How to alter Similar column Or Alter All column in one Query in Entire Database.
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
You may also find following interesting post :
- BISQL # 89 – How to establish / Find relationship between Table and stored Procedure in Database
- List of Monthly post of MS BI, SQL & Link Resource Blog – March 2012
- Free E-Book Microsoft for SQL Server 2012–Download, Information and Content
- BISQL # 88 – Laymen to SQL Developer # 8 – Assignment #1 – Part #7 – Explanation and Basic Theory for Codd’s Rules and Normalization
- BISQL # 87 – Why DB Diagram doesn’t Work !! OR How to enable Database Diagram
- BISQL # 86 – How to Run SSIS package in SQL Server using xp_cmdshell
- List of Monthly post of MS BI,SQL & Link Blog – January 2012
- BISQL # 85 – SQL SERVER – Interview Questions and Answers Book
For More information related to BI World visit my Mentalist Blog
Link Resource Blog >> Daily Interesting links
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
SQL Server Mentalist 