Home > Link, SQL > BISQL # 91 – How to alter Similar column Or Alter All column in one Query in Entire Database , Fast and Furious !

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

imageimageimage

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

image

Following is Query analysis for above result with some explanation

image

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 :

image

Query analysis as follows :

image

After this just copy n paste result and execute all we will get desired result

image

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

Link Resource Website

You may also find following interesting post :

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

| Facebook |Twitter | LinkedIn| Google+ | WordPress | RSS |

About these ads
Categories: Link, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,114 other followers

%d bloggers like this: