SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Alter all tables script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JiltedCitizen
Starting Member

2 Posts

Posted - 05/18/2006 :  15:26:19  Show Profile  Visit JiltedCitizen's Homepage  Reply with Quote
So I needed to add 2 columns to about 20 tables. Not much and I could have easily wrote a script for each table. But thats boring. So I started searching around.

I found up with this from this post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65351

Select 'Alter table '+table_name+' Add yourcol datatype' from information_schema.tables
where table_name<>'dtProperties'




Cool but I wanted it to run and execute at once, not just create the scripts. The only way I could think was is with a cursor. Something I didn't want to do. But did anyway. I came up with this.

DECLARE Alter_tables_cursor CURSOR
   FOR
   select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
 PRINT 'Alter table '+@tablename+' Add ModifiedBy int, ModifiedOn datetime'
 EXEC('Alter table '+@tablename+' Add ModifiedBy int, ModifiedOn datetime')
 FETCH NEXT FROM Alter_tables_cursor INTO @tablename
 
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor


I'm sure there are better ways. If so post them.

http://www.jiltedcitizen.com

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 05/19/2006 :  02:46:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Well. Here is non Cursor Solution


Declare @t table(query varchar(1000))
insert into @t
Select 'Alter table '+table_name+' Add yourcol datatype' from information_schema.tables
where table_name<>'dtProperties'

Declare @sql varchar(1000)
Set @sql=''

While exists
	(
		Select * from @t where query>@sql
	)

Begin
	Select @sql=min(query) from @t where query>@sql
	EXEC(@sql)
End


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SanctiCrucis05
Starting Member

1 Posts

Posted - 09/07/2006 :  12:15:30  Show Profile  Reply with Quote
I'd like to use this script to Drop 3 particular columns from all tables. Unfortunately, of the 50 or so tables, about 5 are lacking the columns and cause the script to cut out. Could I write an If or While clause into one of the above scripts to allow it to finish?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 12/09/2006 :  04:26:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by SanctiCrucis05

I'd like to use this script to Drop 3 particular columns from all tables. Unfortunately, of the 50 or so tables, about 5 are lacking the columns and cause the script to cut out. Could I write an If or While clause into one of the above scripts to allow it to finish?



You need to add where condition

Select 'Alter table '+table_name+' Drop Column yourcol ' from information_schema.Columns where column_name='Yourcol'


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 12/09/2006 :  10:35:07  Show Profile  Visit jezemine's Homepage  Reply with Quote
This seems dangerous to me.

Wouldn't you want to inspect the generated script for correctness first before applying it? In general, I think it's best when doing the "sql-from-sql" (Jay's term) thing to generate sql to a file or an output window, inspect it, and THEN execute it.


http://www.elsasoft.org
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/10/2006 :  01:24:32  Show Profile  Reply with Quote
+1 for inspect the generated script for correctness first before applying it
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000