Hier zwei Beispiele für undokumentierte stored procedures:
sp_MSforeachtable
Die Prozedur "sp_MSforeachtable" geht alle Tabellen einer Datenbank durch und führt einen oder mehrere Befehle aus,
Dies ist die Parameter Syntax für sp_MSforeachtable:
Where:
exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,
@command3, @whereand, @precommand, @postcomman
@RETURN_VALUE - is the return value which will be set by "sp_MSforeachtable"
@command1 - is the first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)
@replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
@command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
@whereand - this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
@precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any table
@postcommand - is also a nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables
(Quelle: http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm)
-- die Zeilen alle Tabellen ausgeben
USE datenbankName
go
create table #rows(tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 'insert into #rows select ''?'', count(*) from ?'
select * from #rows order by tablename
drop table #rows
-- alle Tabellen überprüfen
USE datenbankName
go
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])';
Wenn man dies Procedur in einen Cursor mit allen Datenbanken des Servers einbaut, dann kann man auf diese Weise Befehle auf Tabellen aller Datenbanken absetzen:
Z.B.
declare @dbname varchar(50)
declare @logname varchar(50)
declare @sql varchar(2000)
declare cDB cursor for select name from sys.databases
open cDB
fetch next from cDB into @dbname
while @@fetch_status=0
begin
exec ('use ['+@dbname+']')
exec ('sp_MSForEachTable @command1="dbcc dbreindex (''?'')"')
fetch next from cDB into @dbname
end
close cDB
deallocate cDB
sp_MSforeachtable
Diese Prozedur geht alle Datenbanken eines Servers durch und führt einen oder mehrere Befehle aus:
Syntax:
Where:
exec @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar, @command2,
@command3, @precommand, @postcommand
@RETURN_VALUE - is the return value which will be set by "sp_MSforeachdb"
@command1 - is the first command to be executed by "sp_MSforeachdb" and is defined as nvarchar(2000)
@replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
@command2 and @command3 are two additional commands that can be run against each database
@precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any database
@postcommand - is also an nvarchar(2000) field used to identify a command to be run after all commands have been processed against all databases.
(Quelle: http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm)
Beispiele:
-- Anzeige aller Datenbanken mit den dazugehrigen Tabellen
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', name from sys.tables'
-- alle Datenbanken des Servers überprüfen:
EXEC sp_Msforeachdb "DBCC checkdb ('?')"
-- Datenbanknamen ausgeben
EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME()'
-- Datenbankname, Dateiname und die Dateigröße der Datenbanken einer Instanz ausgeben
EXEC sp_MSforeachdb 'SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
-- alle eigenen Datenbanken sichern
EXECUTE sp_msforeachdb 'USE ? IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'') BACKUP DATABASE ? TO DISK = ''G:?.bak, WITH INIT'''
Weitere undokumentierte stored procedures findet man hier:
http://www.sqlservercurry.com/2010/04/list-of-undocumented-stored-procedures.html
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
http://www.databasejournal.com/features/mssql/article.php/1441251/Useful-Undocumented-Extended-Stored-Procedures.htm