How to execute a TSQL statement for all databases

Share this Post

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


Share this Post

About: ReplTalk


3 thoughts on “How to execute a TSQL statement for all databases”

  1. 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  

  2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.