June 2, 2009 at 2:08 pm
Hi,
1) I want to add a column (e.g. UniName) to tables (loop through all the tables) in a DB.
2) If the table consists that column, skip it otherwise add that column the table.
3) I want to restrict some tables and don't want to add the column.
I have the follwing script which adds UniName to the first table and then returns an error -
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.[/i]
DECLARE @SqlString VARCHAR
SET @SqlString = (SELECTDISTINCT 'ALTER TABLE [' + ss.name + '].[' + OBJECT_NAME(sc.object_id) + ']' + CHAR(10) +
'ADD UniName VARCHAR(50) NOT NULL'
FROMSys.Columns sc
INNER JOIN Sys.Objects so
ON sc.object_id = so.object_id
INNER JOIN Sys.Schemas ss
ON so.schema_id = ss.schema_id
WHEREsc.object_id NOT IN
(
SELECT DISTINCT sc.object_id
FROM Sys.Columns sc
INNER JOIN Sys.Objects so
ON sc.object_id = so.object_id
WHERE sc.name = 'UniName' OR so.Name IN ('CourseSchedule')
)
AND so.type = 'U')
SELECT @SqlString;
Here the subquery returns more than 1 result which is not permissible. Can anyone give me alternate suggestion/solution?
Thanks in advance,
Vishal
June 2, 2009 at 3:06 pm
You can solve it by using a cursor, like this:
DECLARE @sqlstring VARCHAR(MAX);
DECLARE @s-2 VARCHAR(1000);
DECLARE @cur CURSOR;
SET @sqlstring = '';
SET @cur = CURSOR LOCAL FOR
SELECT DISTINCT 'EXEC(''ALTER TABLE [' + ss.name + '].[' + OBJECT_NAME(sc.object_id) + '] ADD UniName VARCHAR(50) NOT NULL'');'
FROM Sys.Columns sc
INNER JOIN Sys.Objects so
ON sc.object_id = so.object_id
INNER JOIN Sys.Schemas ss
ON so.schema_id = ss.schema_id
WHERE sc.object_id NOT IN
(
SELECT DISTINCT sc.object_id
FROM Sys.Columns sc
INNER JOIN Sys.Objects so
ON sc.object_id = so.object_id
WHERE sc.name = 'UniName' OR so.Name IN ('CourseSchedule')
)
AND so.type = 'U';
OPEN @cur;
FETCH NEXT FROM @cur INTO @s-2;
WHILE @@fetch_status = 0 BEGIN
SET @sqlstring = @sqlstring + @s-2 + CHAR(13) + CHAR(10);
FETCH NEXT FROM @cur INTO @s-2;
END
CLOSE @cur;
DEALLOCATE @cur;
PRINT @sqlstring;
June 2, 2009 at 4:41 pm
You were close. Try this instead:
DECLARE @SqlString VARCHAR(MAX)
SELECT @SqlString = 'ALTER TABLE ['
+ su.name + '].['
+ OBJECT_NAME(su.object_id) + ']'
+ CHAR(10) + 'ADD UniName VARCHAR(50) NOT NULL'
FROM (
SELECT DISTINCT ss.name, sc.object_id
FROM Sys.Columns sc
INNER JOIN Sys.Objects so ON sc.object_id = so.object_id
INNER JOIN Sys.Schemas ss ON so.schema_id = ss.schema_id
WHERE sc.object_id NOT IN
(
SELECT DISTINCT sc.object_id
FROM Sys.Columns sc
INNER JOIN Sys.Objects so ON sc.object_id = so.object_id
WHERE sc.name = 'UniName' OR so.Name IN ('CourseSchedule')
AND so.type = 'U'
)
) su
PRINT @SqlString;
EXEC @SqlString;
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 3, 2009 at 6:43 am
Hi Stefan,
Thanks for replying. I tried this method in SSMS and I am getting "Command Completed Successfully" message. But the column is not added to the tables. I refreshed them, closed and start over again but its not working. Can you tell me what am I missing?
Regards,
Vishal
June 3, 2009 at 6:56 am
I also tried your method and it is giving me an error on the line EXEC @SqlString. The error message is -
The name 'ALTER TABLE [dbo].[Course]
ADD UniName VARCHAR(50) NOT NULL' is not a valid identifier.
Can you tell me what is wrong?
Regards,
Vishal
June 3, 2009 at 7:11 am
EXEC @SqlString
should be
EXEC(@SqlString)
Far away is close at hand in the images of elsewhere.
Anon.
June 3, 2009 at 7:14 am
Change the "PRINT @sqlstring;" part in "EXEC (@sqlstring);" or copy/paste output (Message tab) and exec.
June 3, 2009 at 7:16 am
Thanks David.
I forgot to mention that I corrected it to EXEC (@SqlString) but it didn't work. It giving me an error on - DECLARE @SqlString VARCHAR(MAX).
The error message is -
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'UniName' cannot be added to non-empty table 'MSreplication_options' because it does not satisfy these conditions.
Regards,
Vishal
June 3, 2009 at 7:25 am
Yes I was going to mention it but since it was in your post I assumed (incorrectly) that the tables were empty.
The message says it all, you cannot add a NOT NULL column to a populated table
You have to either
Add it as a NULLable column, update it to set it's default values and then alter the column to make it NOT NULL
or
Do as the message states add DEFAULT property to set the column to single default value
Also the query will update all tables including system tables ie 'MSreplication_options' mentioned in the message
Far away is close at hand in the images of elsewhere.
Anon.
June 3, 2009 at 7:40 am
Hi David,
I have 4 tables and none of them contains any record, they are completely empty. I have created them and I am trying to add a column.
What do you think about the solution proposed by Stefan of uding the cursors. I tried it and its giving me the message - Commands Completed Successfully. But its not able to add the column in to the table.
Regards,
Vishal
June 3, 2009 at 7:54 am
@All
I think I figured out and the sql using cursor is working fine...I deleted the column UniName (if it was present) and executed the SQL...it then added colum to all the tables except CourseSchedule (which was opted out). Here is the sql code -
USE Prac
DECLARE @sqlstring VARCHAR(MAX);
DECLARE @s-2 VARCHAR(1000);
DECLARE @cur CURSOR;
SET @sqlstring = '';
SET @cur = CURSOR LOCAL FOR
SELECT DISTINCT 'ALTER TABLE [' + ss.name + '].[' + OBJECT_NAME(sc.object_id) + '] ADD UniName VARCHAR(50) NOT NULL;'
FROM Sys.Columns sc
INNER JOIN Sys.Objects so
ON sc.object_id = so.object_id
INNER JOIN Sys.Schemas ss
ON so.schema_id = ss.schema_id
WHERE sc.object_id NOT IN
(
SELECT DISTINCT sc.object_id
FROM Sys.Columns sc
INNER JOIN Sys.Objects so
ON sc.object_id = so.object_id
WHERE sc.name = 'UniName' OR so.Name IN ('CourseSchedule')
)
AND so.type = 'U';
OPEN @cur;
FETCH NEXT FROM @cur INTO @s-2;
WHILE @@fetch_status = 0 BEGIN
SET @sqlstring = @s-2 + CHAR(13) + CHAR(10);
EXEC (@SqlString);
FETCH NEXT FROM @cur INTO @s-2;
END
CLOSE @cur;
DEALLOCATE @cur;
Thanks again to all of you for putting in your valuable inputs/suggestions/solutions.
Regards,
Vishal
June 3, 2009 at 9:05 am
David Burrows (6/3/2009)
EXEC @SqlStringshould be
EXEC(@SqlString)
Yep, my bad on that one. Thanks for covering for me David!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 3, 2009 at 12:23 pm
Hi Vishal,
You're using variable @sqlstring now, but it's not necessary (it makes the code harder to read). But if it works it works...
Still, I believe it can work as follows:
FETCH NEXT FROM @cur INTO @s-2;
WHILE @@fetch_status = 0 BEGIN
--SET @sqlstring = @s-2 + CHAR(13) + CHAR(10);
SET @sqlstring = @sqlstring + @s-2 + CHAR(13) + CHAR(10);
--EXEC (@SqlString);
FETCH NEXT FROM @cur INTO @s-2;
END
EXEC (@SqlString);
or remove all lines regarding @sqlstring and change the loop as follows:
June 3, 2009 at 12:36 pm
Thanks Stefan
Yeah its easy to read without @SqlString
I removed it and now I am using what you suggested (in the second block).
Regards,
Vishal
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply