[dba-SQLServer] Adding a field to every table

John

Look this up

sp_msforeachtable

Martin


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: 19 January 2012 15:08
To: Sqlserver-Dba; VBA; Access Developers discussion and problem solving
Subject: [dba-SQLServer] Adding a field to every table

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
http://www.databaseadvisors.com