March 29, 2010 at 3:37 am
As part of an application change I need to add and remove some columns from a number (50+) tables of similar structure.
I can do it manually of course, and it wouldn't take THAT long... but it got me thinking.
Is there some way that I could do this to multiple tables?
Paul
March 29, 2010 at 3:54 am
You can write down as script and schedule the same in a job.
March 29, 2010 at 3:59 am
You can create a temporary table and insert all the table names in that and use dynamic query for that.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 29, 2010 at 3:59 am
I am sorry... but I don't know what you mean.
:ermm:
March 29, 2010 at 4:27 am
Ah I see. Ok thanks.
Is it possible that you could show me an example such a query?
March 29, 2010 at 4:27 am
Can you go by below -
IF EXISTS ( SELECT 1 FROM sys.objects WHERE Object_ID = OBJECT_ID('tempdb..#tablename') )
BEGIN
DROP table #tablename
END
CREATE table #tablename (name varchar(100))
GO
INSERT INTO #tablename
SELECT 'table1'
UNION ALL
SELECT 'table2'
UNION ALL
SELECT 'table3'
DECLARE @sql AS VARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql + ' ALTER TABLE ' + name + ' DROP COLUMN col1 ' FROM #tableName
EXEC ( @sql )
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 29, 2010 at 4:39 am
Thank you for your swift reply.
I will give this a try.
Paul
March 29, 2010 at 5:42 am
Thank you very much Vaibhav K T
That did the trick swiftly and accurately. It must have saved me hours.
:-):-)
March 29, 2010 at 5:48 am
This code is not having loop to alter all the tables you want.
Please check the code logic.
Alter statement should be fired for each table
March 29, 2010 at 5:52 am
Sorry!! its working- giving all statements in one line.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply