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

Totally true.  I just did it as an SP in case someone else
needed it.  They could copy and paste.


At 02:00 PM 2/9/2012, you wrote:
>Date: Mon, 30 Jan 2012 08:20:40 -0800
>From: Francisco Tapia <fhtapia at gmail.com>
>To: Discussion concerning MS SQL Server
>         <dba-sqlserver at databaseadvisors.com>
>Subject: Re: [dba-SQLServer] Adding a field to all user tables
>Message-ID:
>         <CAP+kXmaZE3qDPhvMmOr_T-eiaz=B15qWdX9KK01SE9CBHxjezg at mail.gmail.com>
>Content-Type: text/plain; charset=ISO-8859-1
>
>out of curiosity would you really make this a sproc?  I think more of a
>maintenance architecture script (again this is nitpicking) but I gather
>that once you've added your timestamps to all your tables, going forward
>you'd automatically do that to all new tables created, right?
>
>in the case of a broken design, where some tables have the timestamps, and
>many do not you'd probably want to skip the timestamped tables.
>
>how about a little mod like:
>
>SELECT t.name AS TableName
>--    ,sc.name AS SchemaName,
>--    c.name AS ColumnName,
>--    types.name AS TypeName,
>--    st.name AS TypeSchemaName,
>--    t.type AS type
>FROM sys.all_columns c INNER JOIN sys.all_objects t WITH (NOLOCK) ON
>c.object_id=t.object_id
>LEFT JOIN sys.schemas sc WITH (NOLOCK) ON t.schema_id=sc.schema_id
>LEFT JOIN sys.types types WITH (NOLOCK) ON c.user_type_id=types.user_type_id
>LEFT JOIN sys.schemas st WITH (NOLOCK) ON st.schema_id=types.schema_id
>WHERE t.type IN ('U')
>AND types.NAME <> 'timestamp'
>GROUP BY t.NAME
>ORDER BY t.NAME
>
>instead of
>SELECT name
>   FROM sysobjects
>   WHERE [TYPE] = 'U'
>              ORDER BY name
>
>if you plan on keeping the design with a managed script so you can
>automatically come back and re-add timestamps on any table you may have
>forgotten, you can replace the previous cursor select with this modified
>one, so that you only find the tables without timestamps already.
>
>-Francisco
>http://bit.ly/sqlthis   | Tsql and More...
><http://db.tt/JeXURAx>
>
>
>
>
>On Sat, Jan 28, 2012 at 00:37, Arthur Fuller <fuller.artful at gmail.com>wrote:
>
> > 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
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>

Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com