Being Mark Cohen

[Mostly just a "note to self" for next time, stating the obvious].

If you ever need to iterate through tables in SQL and perform an action here’s a simple script that will do the trick. This particular script will do a reindex on every table in the database.

DECLARE @Table NVARCHAR(100)
DECLARE @sql NVARCHAR(100)
DECLARE tables CURSOR
FOR
        SELECT
                name
        FROM
                sysobjects
        WHERE
                type = ‘u’
OPEN tables
FETCH NEXT FROM tables INTO @Table

WHILE @@FETCH_STATUS = 0
BEGIN
        SELECT @sql = ‘DBCC dbreindex (‘ + @Table + ‘)’;
        BEGIN TRY
                PRINT @Sql;
                EXEC sp_executesql @sql
        END TRY
        BEGIN CATCH
                PRINT ‘Error encountered executing:’ + @Sql;
        END CATCH
        FETCH NEXT FROM tables INTO @Table
END

CLOSE tables
DEALLOCATE tables

You could just as easily make this iterate through stored procedures, or any object type by changing the type = ‘u’ to say type = ‘p’ for stored procedures, etc. Also if you want particular objects then the where clause could include a clause saying AND name like ‘up_%’

You could also easily use something like this to generate scripts for sets of items based on a naming convention, say

DECLARE @Proc NVARCHAR(100)
DECLARE @sql NVARCHAR(100)
DECLARE Procs CURSOR FOR

SELECT
    name
FROM
    sysobjects
WHERE
    type = ‘p’
    AND name like ‘report_%’
OPEN Procs

FETCH NEXT FROM Procs INTO @Proc

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sql = ‘sp_helptext ‘ + @Proc;
    BEGIN TRY
        PRINT ‘IF EXISTS (SELECT * FROM sysobjects WHERE NAME = ”’ + @Proc + ”’)’
        PRINT ‘ DROP PROCEDURE ‘ + @Proc
        PRINT ‘GO’ + CHAR (10)
        EXEC sp_executesql @sql
        PRINT CHAR(10) + CHAR(10) + ‘GO’ + CHAR (10) + CHAR (10)
    END TRY
    BEGIN CATCH
        PRINT ‘– // Error encountered executing: ‘ + @Sql + ‘ //–’;
    END CATCH
    FETCH NEXT FROM Procs INTO @Proc
END

CLOSE Procs
DEALLOCATE Procs

Although for this to be useful you would need to run from command-line, or turn off results headings and use text results in SQL Management Studio