SQL Reloaded: Running a Statement for Each Database (CLR Version)

    I continue the series of posts on the use of the dbo.ExecuteScalarTo  CLR functions created in a previous post, this time, as is mentioned in the title, by running a statement for each database. The old-fashion method of a statement for each database is with the help of undocumented stored procedure sp_MSforeachdb, see for example ReplTalk’s post found recently. Actually the respective example based on the use of DatabaseProperty function has been given only for exemplification, the values available through the DatabaseProperty function could be retrieved directly from the sys.databases system table: 

 -- querying directly the values 
SELECT database_id 
, name DatabaseName 
, Is_Fulltext_enabled IsFulltextEnabled 
, Is_Ansi_Null_Default_on IsAnsiNullDefault 
, Is_Ansi_Nulls_on IsAnsiNullsEnabled 
, Is_Ansi_Warnings_on IsAnsiWarningsEnabled 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

    Here’s the same query rewritten using the DatabaseProperty function:

 -- DatabaseProperty function at work 
SELECT database_id 
, name DatabaseName 
, DATABASEPROPERTY( name ,'IsFulltextEnabled') IsFulltextEnabled 
, DATABASEPROPERTY( name ,'IsAnsiNullDefault') IsAnsiNullDefault  
, DATABASEPROPERTY( name ,'IsAnsiNullsEnabled') IsAnsiNullsEnabled  
, DATABASEPROPERTY( name ,'IsAnsiWarningsEnabled') IsAnsiWarningsEnabled 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

    The same query could be rewritten using the dbo.ExecuteScalarToString function:  

 -- DatabaseProperty function within CLR function 
SELECT database_id 
, name DatabaseName 
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsFulltextEnabled'')') IsFulltextEnabled 
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsAnsiNullDefault'')') IsAnsiNullDefault  
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsAnsiNullsEnabled'')') IsAnsiNullsEnabled  
, dbo.ExecuteScalarToString('SELECT DATABASEPROPERTY( ''' + name + ''',''IsAnsiWarningsEnabled'')') IsAnsiWarningsEnabled 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

    The usefulness of the dbo.ExecuteScalarToString is minimal in this case, however it could be considered more “complex” examples. For example retrieving a count of the number of tables, views, assemblies or users existing in a database:

-- running a statement for each database 
SELECT database_id 
, name DatabaseName 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.tables') NumberTables 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.views') NumberViews 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.assemblies') NumberAssemblies 
, dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.sysusers') NumberTables 
FROM master.sys.databases  
WHERE name LIKE 'AdventureWorks%' 

   Now it depends on each developer’s inspiration and on the problem attempting to solve. I find dbo.ExecuteScalarToString CLR function quite useful when attempting to run for each database multiple queries that returns a single value. On the other side sp_MSforeachdb provides more flexibility in what concerns the types of queries run, though specific techniques (e.g. temporary tables or table variables) need to be used in order to retrieve sp_MSforeachdb’s output in a single result-set (see for example the Locally Fragmented Indexes example from MSDN’s article on Uncover Hidden Data To Optimize Application Performance).