RSS

Dynamic Alter Query for all columns in database

15 Jan

 

I submit this SQL statements maybe it’s useful for you all

it’s dynamic query which used to change current data types for selected columns in tables with specific name to new data types and you can make a lot of things by this SQL Statements

Example

declare @tb nvarchar(200),@col nvarchar(200)

DECLARE Table_Cursor CURSOR FOR

/*Select columns you want to change and put them in cursor*/

SELECT t.name tb, c.name col FROM sys.tables t inner join sys.columns c on t.object_id=c.object_id where c.name like ‘columnPrefix%’

order by tb

OPEN Table_Cursor;

FETCH NEXT FROM Table_Cursor into @tb,@col;

WHILE @@FETCH_STATUS = 0

BEGIN

/*Start Change Current data types to new data types */

select @tb,@col

Execute(‘ALTER TABLE ‘+@tb+‘ ALTER COLUMN ‘+@col+

nvarchar(300) null’) ;

FETCH NEXT FROM Table_Cursor into @tb,@col;

END;

CLOSE Table_Cursor;

DEALLOCATE Table_Cursor;

GO

Advertisements
 
Leave a comment

Posted by on January 15, 2010 in Cursor, OLTP, SQLServer, T-SQL

 

Tags: , ,

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

 
%d bloggers like this: