Paul (psampson@uecomm.com.au) writes:
[quoted text, click to view] > Hi, I'm trying to add a modified datetime and userid to all 72 tables in
> my SQL 2000 database. I have the script to do one table, and a cursor,
> but it won't run across all tables. Any help would be appreciated.
There are a number of errors in your script:
[quoted text, click to view] > DECLARE @tName varchar(40)
> DECLARE C1 CURSOR FOR
While not an error, I recommend that you make your cursors INSENSITIVE
as a matter of routine. The default keyset-driven cursors can sometimes
give nasty surprises.
[quoted text, click to view] > select name from sysobjects where type = 'U'
> OPEN C1
> FETCH NEXT FROM C1 INTO @tName
> -- Check @@FETCH_STATUS to see if there are any more rows to fetch
> WHILE @@FETCH_STATUS = 0
I recommend that you write cursor loops as
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @var1, @var2....
IF @@fetch_status <> 0
BREAK
-- Real job follows here.
END
DEALLOCATE cur
By only having one FETCH statement you make your code safer, because it's
easy to change the SELECT statement, and the new column to the first
FETCH, but forget the second, which may be the screens below.
[quoted text, click to view] > BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
There is no point in executing the SET statements in the loop, and
there is no point to make this a transaction. Not that it is wrong
either.
[quoted text, click to view] > BEGIN TRANSACTION
> ALTER TABLE @tName ADD
> ModifiedDT datetime NULL,
> ModifiedUserID int NULL
> GO
Here are two serious flaws: ALTER TABLE does not accept a variable.
You need to use dynamic SQL for this. (Or sp_MSforeachtable.)
And the GO there is completely out of place. GO is not an SQL command,
but an instruction to the query tool to separate the commands into
different batches. Thus, this batch will fail with a compilation
error, because the BEGIN after WHILE does not have an END.
[quoted text, click to view] > FETCH NEXT FROM C1
And if you thought what I said about FETCH above was silly, look here!
Here you don't insert into a variable, but produce a result set.
--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se
Books Online for SQL Server SP3 at
