Here "USE" is Dynamic.
sp_msforeachdb 'Use
[?];
begin
select ''?'' as DBNAME
end
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id)
AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID =
c.OBJECT_ID
WHERE c.name LIKE ''%exe%''
ORDER BY schema_name, table_name;'
============================================
sp_MSforeachdb
Examples
Without
using cursor we can execute a command in all the databases or on required
databases by using sp_MSforeachdb.
Examples
sp_MSforeachdb 'select ''?''' -- List all databases that are in the
instance. '?' Indicated Database Name
sp_MSforeachdb 'Use [?]; exec sp_spaceused' -- Example to execute any
stored procedure
sp_MSforeachdb 'Use [?]; select * from sysfiles' -- Example to execute
any SQL Statement
sp_MSforeachdb 'Use [?]; Create table ForEachDBTest(Id Int)' -- Creating
a table in all databases
sp_MSforeachdb 'Use [?]; select * from ForEachDBTest' -- Selecting
rows from a table through all databases.
sp_MSforeachdb 'If ''?'' like ''TempDB'' -- Conditional wise
execution
begin
select * from sysfiles
end '
Below command is used to display list of databases which has Create Statistics
property is set to True
EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'',
''ISAutocreateStatistics'')=1
begin
Print ''?''
end'
