How to execute a TSQL statement for all databases

I wanted a quick way to execute a TSQL command against all databases.  I was having hard time getting the sp_MSforeachdb to work and didn’t want to write a cursor,  so I instead built a TSQL command which makes the commands I need to execute.  Not fancy, but it got the job done.

--Command to be exeuted on all databases
SELECT DATABASEPROPERTY( 'Northwind' ,'IsFulltextEnabled')
--undocumented command, but hard to get syntax correct with imbedded ' (quotes)
sp_MSforeachdb "SELECT DATABASEPROPERTY( ?  ,'IsFulltextEnabled')"
--Use statement below to build the TSQL commands to be executed
--Set Output-to-Text before running.
select 'SELECT DATABASEPROPERTY( ' + char(39) + name + char(39) + ',' + char(39)
  + 'IsFulltextEnabled' + char(39) + ')'
  from sysdatabases

Read more: REPLTalk covers Using and Tuning SQL Replication

Posted via email from .NET Info