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.

Skip to main content