[dba-SQLServer] Adding a field to all user tables

I'll give you this one, for sure! It's guaranteed to be a smallish cursor,
even in a large-ish database. Maybe 600 tables in the largest db I ever
worked on! And as cursors go, that's not large. That said, I personally
would have gone with a ForEach in SMO, but that's nit-picking. Well done!

P.S.
I also like your formatting conventions.

On Fri, Jan 27, 2012 at 12:54 PM, Robert Stewart <rls at webedb.com> wrote:

> For whatever reason, my original post of this never seems to appear on the
> results from the list.
>
> While I do not advocate using cursors 99% of the time, there are times
> that they are what you should use.
> This will make the code easier to read, and it will work smooth.
>
> CREATE PROCEDURE dbo.usp_AddTimestamp
> AS
> BEGIN
>    SET NOCOUNT ON;
>    DECLARE
>            @TableName VARCHAR( 128 )
>          , @Sql NVARCHAR( 4000 )
>
>    DECLARE curTableList CURSOR
>        FOR SELECT name
>              FROM sysobjects
>              WHERE [TYPE] = 'U'
>              ORDER BY name
>
>    OPEN curTableList
>
>    FETCH next FROM curTableList INTO @TableName
>
>    WHILE @@FETCH_STATUS = 0
>        BEGIN
>            SET @Sql = N'ALTER TABLE dbo.' + @TableName + ' ADD Timestamp'
>
>            EXEC sp_executesql @Sql
>
>            FETCH next FROM curTableList INTO @TableName
>        END
>
>    CLOSE curTableList
>    DEALLOCATE curTableList
> END
> GO--

Arthur
Cell: 647.710.1314

Prediction is difficult, especially of the future.
  -- Niels Bohr