Comments on Christopher Miller's random thoughts: How to rename database objects to comply with nami...

Those two while loops are different, though. @@FE...

2011-07-15T18:43:12.743-04:00

Those two while loops are different, though. @@FETCH_STATUS is undefined if you haven't called fetch yet, so SQL Server could rightfully return any value and your loop wouldn't execute. Also, if you wrote two consecutive loops the second wouldn't execute because @@FETCH_STATUS <> 0 after the first finishes executing.

"while 1 = 1" is uglier than a "while (true)" in other languages, but this little bit of boilerplate simplifies maintenance if you ever need to change the fetch statement.

I concur with the last "anonymous". Writing a whi...

2008-07-08T10:33:00.000-04:00

I concur with the last "anonymous". Writing a while/end loop using the syntax "while (1=1)" gains nothing in performance and makes the code harder to read.

You have gone from a single possible test to exit the loop to two, which increases the complexity of the code without any measurable benefit. The fetch next statement is still being performed for the same number of rows either way.

At the end of the day, you go with the code that you and your team can read 6 months from now and still understand.

When did SQL developers suddenly begin to think th...

2008-07-08T09:59:00.000-04:00

When did SQL developers suddenly begin to think that this WHILE loop:
WHILE (1=1)
BEGIN
FETCH NEXT FROM FixCursor
INTO @OldName, @NewName
IF (@@FETCH_STATUS <> 0) BREAK

is any different than this one:
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM FixCursor
INTO @OldName, @NewName
...
END

Someone somewhere along the line thought that it performed better and wasn't a real cursor. Hogwash, it's still a cursor and still loops each record. It's also harder to follow.

If you use the following code, you can also select...

2008-07-08T08:04:00.000-04:00

If you use the following code, you can also select tablenames, indexnames and so on to be renamed into @FixTable.

DECLARE @FixTable TABLE
(
sys_OldName [sysname] NOT NULL,
sys_NewName [sysname] NOT NULL
)
INSERT INTO @FixTable (sys_OldName, sys_NewName)
SELECT o.name AS OldName, (N'DF_' + Object_Name(o.parent_obj) + N'_' + c.Name) AS NewName
FROM sysobjects o
JOIN syscolumns c ON o.parent_obj = c.id AND c.cdefault = o.id
WHERE o.type = 'D' AND
o.name <> (N'DF_' + Object_Name(o.parent_obj) + N'_' + c.Name)

DECLARE @OldName NVARCHAR (1024)
DECLARE @NewName NVARCHAR (1024)
DECLARE FixCursor CURSOR LOCAL FOR
SELECT sys_OldName, sys_NewName
FROM @FixTable

OPEN FixCursor
WHILE (1=1)
BEGIN
FETCH NEXT FROM FixCursor
INTO @OldName, @NewName
IF (@@FETCH_STATUS <> 0) BREAK

SET @OldName = N'[' + @OldName + N']'
EXECUTE sp_rename @objname = @OldName,
@newname = @NewName,
@objtype = N'OBJECT'
END
CLOSE FixCursor
DEALLOCATE FixCursor

You use two FETCH NEXT statements wich are exactly...

2008-07-08T07:08:00.000-04:00

You use two FETCH NEXT statements wich are exactly the same. By writing the loop a little different, only one FETCH NEXT is necessary:

WHILE (1=1)
BEGIN
FETCH NEXT
FROM AddDrop
INTO @OldName, @NewName
IF (@@FETCH_STATUS <> 0) BREAK

...
END

Excellent script!!!

Thanks mate - you've sa...

2008-06-24T18:10:00.000-04:00

Excellent script!!!

Thanks mate - you've saved me the hassle of reinventing the wheel

By the way, I personally have trouble reading code...

2008-06-11T13:06:00.000-04:00

By the way, I personally have trouble reading code that has gratuitous table aliases.

Isn't the following much easier for a human to read and understand, than the original?

INSERT INTO @FixSchema(sys_OldName, sys_NewName)
select sysobjects.name AS OldName, 'DF_' + Object_Name(sysobjects.parent_obj) + '_' + syscolumns.Name AS NewName,
sysobjects.type
FROM sysobjects
JOIN syscolumns ON sysobjects.parent_obj = syscolumns.id AND syscolumns.cdefault = sysobjects.id
WHERE sysobjects.type = 'D'

Great tool! I had to update it slightly to includ...

2008-05-08T15:44:00.000-04:00

Great tool! I had to update it slightly to include the schema name since MY default schema is not the same as the default schema for the part of the database that I wanted to run this on.

But it was great otherwise.