[dba-SQLServer] add timestamp column to all tables

More or less that is the approach to use.  One could quibble here and there about the specifics of your version, but it gets the job done and you've learned some things in the process so I'd say 'well done'.
Here is another solution that you haven't heard of (by design of MS), but it is simpler.

sp_msforeachtable.  A link that shows an application is
http://weblogs.sqlteam.com/joew/archive/2007/10/23/60383.aspx


Message: 9
Date: Thu, 19 Jan 2012 10:07:48 -0500
From: jwcolby <jwcolby at colbyconsulting.com>
To: Sqlserver-Dba <dba-sqlserver at databaseadvisors.com>, VBA
        <dba-vb at databaseadvisors.com>,  Access Developers discussion and
        problem solving <accessd at databaseadvisors.com>
Subject: [dba-SQLServer] Adding a field to every table
Message-ID: <4F1831C4.5060003 at colbyconsulting.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

I have built several SQL Server databases which are used by Access, and have just recently
discovered that I really need a timestamp in every table.  So I have been laboriously adding a
timestamp field table by table.

As everyone knows, TSQL is not my strength, but I went out looking for solutions to doing this
programmatically using TSQL.  So the challenge is to get every user table in a database, and then
execute an alter table statement:

alter table EachTableName add timestamp

I managed to do it but boy is it *not* elegant!

I found the following (by our own Arthur Fuller)  which creates a udf to return a table with the
names of all the users tables.  My first iteration actually created this udf and then called it.

http://www.techrepublic.com/article/alter-every-table-in-a-sql-database/5796376

The following discusses iterating through a table.  The author was executing a stored procedure for
each line of the table, which is close to what I am trying to do.  Yes, I know RBAR and all that but
after all this is just a minimal number of operations done once per database.

http://weblogs.asp.net/jgalloway/archive/2006/04/12/442618.aspx

That said, I would like to know if there is an elegant (set based) way to execute a line of code
like I am doing for each record in the table.

If you promise not to laugh I will show you my cobbled together solution.  I built a User Stored
Procedure so that I can just copy it over to my server at the client.  The stored procedure creates
the udf every time it runs, this in case this is the first time I am running the USP.  I then
declare a table to store the table returned by the UDF, and fill the table.  After that I iterate
the table RBAR, pulling the assembled SQL statement out into a varchar, and then exec() the SQL
Statement.

-- =============================================
-- Author:              <jwc>
-- Create date: <1/19/2012>
-- Description: <Add a timestamp field to every user table>
-- =============================================
ALTER PROCEDURE [dbo].[usb_AddFldTake2]
        @DBName as varchar(250)
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

DECLARE @SQL varchar(4000)
DECLARE @tblUsrTableNames table (rownum int IDENTITY (1, 1), TblName varchar(250))

--Get all of the table names into a table variable

        select @SQL = 'SELECT TOP 100 PERCENT name
                FROM ' + @DBName + '.dbo.sysobjects
                WHERE type = ''U''
                ORDER BY name'
        print @SQL

        insert into @tblUsrTableNames (TblName) exec (@SQL)

--Set up RBAR table iteration
declare @RowCnt int
declare @MaxRows int

        select @RowCnt = 1
        select @MaxRows=count(*) from @tblUsrTableNames

Declare @TblName varchar(250)

        while @RowCnt <= @MaxRows
        begin
                --Get the name of the table from each row
            select @TblName = (SELECT TblName from @tblUsrTableNames where rownum = @RowCnt )
            --build a sql statement to perform the alter table and add the timestamp
            select @SQL = 'ALTER TABLE PrisonMinistries.dbo.[' + @TblName + '] ADD timestamp'
                print @SQL
                --Execute the sql
                execute (@SQL)
                --move to the next row
                Select @RowCnt = @RowCnt + 1
        end

     -- Insert statements for procedure here
END

As I mentioned I would like to know if there is an elegant (set based) way to execute a line of code
like I am doing for each record in the table.

--
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it



------------------------------

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver


End of dba-SQLServer Digest, Vol 106, Issue 3
*********************************************

The contents of this e-mail message and its attachments are covered by the Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended solely for the addressee(s) hereof. If you are not the named recipient, or the employee or agent responsible for delivering the message to the intended recipient, or if this message has been addressed to you in error, you are directed not to read, disclose, reproduce, distribute, disseminate or otherwise use this transmission.  If you have received this communication in error, please notify us immediately by return e-mail or by telephone, 530-893-3520, and delete and/or destroy all copies of the message immediately.