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
Wrap your ? in quotes.
sp_MSforeachdb "SELECT DATABASEPROPERTY( '?' ,'IsFulltextEnabled')"
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
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.