How to execute a TSQL statement for all databases


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’) as ‘?'” 

 

–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

 

–Output
SELECT DATABASEPROPERTY( ‘Northwind’,‘IsFulltextEnabled’)
SELECT DATABASEPROPERTY( ‘distribution’,‘IsFulltextEnabled’)
SELECT DATABASEPROPERTY( ‘OrdersSub’,‘IsFulltextEnabled’)
SELECT DATABASEPROPERTY( ‘CustomersEmployeeSub’,‘IsFulltextEnabled’)

Chris Skorlinski
Microsoft SQL Server Escalation Services

Comments (3)

  1. Slappy says:

    Wrap your ? in quotes.

    sp_MSforeachdb "SELECT DATABASEPROPERTY( '?'  ,'IsFulltextEnabled')"

  2. noeldr says:

    if all you want is to "print" text why not use sys.databases

    select  'blah blah …'

    from sys.databases

    Where …. — is_full_text_enabled =1  

  3. Adrian says:

    Recently I wrote a simple CLR function that executes a query and returns a (scalar) string data type, thus the function could be used to create dynamic queries based on the attributes from a given table. The function could be run against the sys.databases table as follows:

    SELECT database_id

    , name DatabaseName

    , dbo.ExecuteScalarToString('SELECT count(1) FROM ' + name + '.sys.tables') NumberTables

    FROM master.sys.databases

    WHERE name LIKE 'AdventureWorks%'

    You can find the link to function's definition by using the following link: sql-troubles.blogspot.com/…/running-statement-for-each-database-clr.html.