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

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
>
>