About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Thursday, July 22, 2010

Running a Statement for Each Database – The CLR Version

    I continue the series of posts on the use of the dbo.ExecuteScalarTo<DataType>  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%'

CLR  UDF - for each database
    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).

No comments: