/********************************************************************************************
-- Title : [2k8] sp_MSforeachdb, sp_MSforeachtable 사용 예제
-- Reference : dbrang.tistory.com
-- Key word : sp_msforeachdb sp_msforeachtable for each db for each table
********************************************************************************************/
EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"
EXEC sp_MSforeachdb @command1="EXEC SP_HELPDB '?'"
EXEC sp_MSforeachdb @command1="select * from [?].dbo.sysfiles"
EXEC sp_MSforeachdb 'select * from [?].dbo.sysfiles'
EXEC sp_MSForEachDB 'select * from [?].sys.tables;';
EXEC sp_MSForEachDB 'Use [?]; EXEC sp_helpfile;';
EXEC sp_MSForEachDB 'Use [?]; EXEC sp_helpfile;';
EXEC sp_MSforEachTable 'EXEC sp_spaceused [?];';
GO
-->> Case 1. <<--
declare @ttt table (db_nm sysname, tbl_nm sysname);
insert into @ttt
EXEC sp_MSForEachDB
'select ''?'' "db_nm", name from [?].sys.tables
where ''?'' not in (''master'', ''tempdb'', ''msdb'', ''model'');
';
select * from @ttt;
-->> Case 2. <<--
CREATE TABLE #ttt
(dbname SYSNAME, filename SYSNAME, fileid SYSNAME);
EXEC sp_MSforeachdb
'USE [?]; INSERT INTO #ttt SELECT ''?'', fileid, name FROM dbo.sysfiles';
SELECT * FROM #ttt;
-->> Case 3. <<--
EXEC sp_MSForEachTable
' if (''?'' = ''[schema_name].[table_name]'')
select ''?'';
';
EXEC sp_MSForEachTable 'exec (''select top 10 * from ?'') ';