Using sp_MsForEachDb and sp_MsForEachTable Together

Ever wanted to run a T-SQL command on every user table in every user database? Here's how to nest the undocumented sp_MsForEachTable system stored procedure inside the undocumented sp_MsForEachDB system stored procedure.

DECLARE @Sql NVARCHAR(4000) = 
      'IF EXISTS (SELECT * FROM sys.databases WHERE name = ''''#'''' AND owner_sid = 0x01) RETURN '
    + 'RAISERROR(''''Reclaiming space on table ' + QUOTENAME('#') + '.?'''', 10, 1) WITH NOWAIT '
    + 'USE ' + QUOTENAME('#') + ' '
    + 'DBCC CLEANTABLE(''''#'''', ''''?'''', 10000) WITH NO_INFOMSGS '

SET @Sql = 'USE ' + QUOTENAME('#') + ' EXEC sp_MsForEachTable ''' + @Sql + ''''

EXEC sp_MsForEachDb @Sql, @replacechar = '#'

The trick is the @replacechar parameter to the sp_MsForEachDb stored procedure: it's how we keep the placeholders for the database and the table separate. If there's an easier way, I'd love to see it.

Use QUOTENAME( )... [or else!]

Lately, I seem to be reading a lot of "bracket-slapping" code:

SET @Sql = 'ALTER INDEX [' 
         + @IndexName  + '] ON [' 
         + @DBName     + '].[' 
         + @SchemaName + '].['
         + @ObjectName + '] REORGANIZE '

This is much easier to read (and easier to type!) than using the QUOTENAME() function:

SET @Sql = 'ALTER INDEX ' 
         + QUOTENAME(@IndexName)  + ' ON ' 
         + QUOTENAME(@DBName)     + '.' 
         + QUOTENAME(@SchemaName) + '.'
         + QUOTENAME(@ObjectName) + ' REORGANIZE '

BOL says that QUOTENAME() "[r]eturns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server delimited identifier." So these two code snippets are pretty much interchangable, right?

But QUOTENAME() would be pretty lame if all it did was slap brackets around a string, wouldn't it? Fortunately (or unfortunately, if you've got a lot of "bracket-slapping" code laying around) it does more: it "escapes" embedded bracket characters. For example, let's take the table name 'Sales YTD [2009]', and try to create it.

-- Demonstrate that QUOTENAME() is safer than bracket-slapping.
DECLARE @Sql      NVARCHAR(4000) = ''
DECLARE @TableOne sysname        = 'Sales YTD [2009]'

-- Expected to fail: table name has spaces, brackets.
SET @Sql = 'CREATE TABLE '  + @TableOne            + ' (x INT)'
PRINT '(1)  ' + @Sql
EXEC sp_executesql @Sql
PRINT ' '

-- Expected to fail: bracket-slapping doesn't handle brackets.
SET @Sql = 'CREATE TABLE [' + @TableOne            + '] (x INT)'
PRINT '(2)  ' + @Sql
EXEC sp_executesql @Sql
PRINT ' '

-- Expected to succeed.
SET @Sql = 'CREATE TABLE '  + QUOTENAME(@TableOne) + ' (x INT) '
         + CHAR(10) + '     '
         + 'DROP   TABLE '  + QUOTENAME(@TableOne)
PRINT '(3)  ' + @Sql
EXEC sp_executesql @Sql
PRINT ' '

The first attempt uses the name with no "decoration"; it's not a legal identifier name, and so will fail. The second attempt uses "bracket-slapping"; the brackets in the table's name will cause this to fail. The third, and correct, method uses QUOTENAME(). Running the code above results in this output:

(1)  CREATE TABLE Sales YTD [2009] (x INT)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'YTD'.
 
(2)  CREATE TABLE [Sales YTD [2009]] (x INT)
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Sales YTD [2009] (x INT)'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Sales YTD [2009] (x INT)'.
 
(3)  CREATE TABLE [Sales YTD [2009]]] (x INT) 
     DROP   TABLE [Sales YTD [2009]]]

Moral of the story: always use QUOTENAME() - unless you enjoy bug hunts!

Find NOT NULL Columns Without DEFAULT Constraints

This is really handy when you have a large legacy database where the previous owner forgot to add DEFAULT constraints to the NOT NULL columns.

This script has been moved to the Schema Analysis scripts page.