[dba-SQLServer] add timestamp column to all tables

Martin Reid mwp.reid at qub.ac.uk
Thu Jan 19 14:55:08 CST 2012


John

Have a look st this one

http://www.sqlservercentral.com/Forums/Topic1090648-391-1.aspx

Martin

Sent from my Windows Phone
________________________________
From: jwcolby
Sent: 19/01/2012 18:13
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] add timestamp column to all tables

If I could get it working this would be magnificent.

http://weblogs.sqlteam.com/joew/archive/2007/10/23/60383.aspx

I tried this:

use InmateCheckout
execute sp_MSforeachtable 'alter table [?] add timestamp'

and it gives the following error

Msg 4902, Level 16, State 1, Line 1
Cannot find the object "[dbo].[tblLocation]" because it does not exist or you do not have permissions.

for each table in that database. It appears that it is in fact iterating all of the tables, but
doesn't have the permissions to perform an alter table kind of sql statement?

John W. Colby
Colby Consulting

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

On 1/19/2012 10:37 AM, David Lewis wrote:
> 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

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



More information about the dba-SQLServer mailing list