Useful undocumented procedures in SQL Server 2000
Before I start, please do not use these procs in your production code since they are undocumented, which means Microsoft does not support these procs and might remove it in future versions. With that said, I am just writing about three procedures which I find very useful. Its more of a shortcut for the DBAs to get their work done faster :)
1. sp_MSForEachDB
This proc executes the statement you give as a parameter for each of the database that are there in the server.
Example:
sp_MSForEachDB 'use ? exec sp_spaceused'
The ? is replaced with the database name for each of the database and then the string is executed and the result is displayed. The above command returns the output of the proc sp_spaceused for all the databases
Note: Please check the comment given by Aaron (below) before using this stored procedure.
2.sp_MSForEachTable
This proc executes the statement you give as a parameter for each of the table that are there in the database.
Example:
sp_MSForEachTable 'select count (*) as ''?'' from ?'
The above statement returns the rowcount of all the tables in the database with the column name as table name. Remember that it will be a seperate result set for the rowcount for each of the table.
3.xp_ExecResultSet
This proc accepts 2 paramters. The query and the database on which to run the query. This is an extended stored procedure residing in the master database. Each of the row resulting from the query will be executed and the result will be displayed
Example:
master..xp_execresultset 'select ''exec sp_helptext '' + name from sysobjects where type = ''p''',
The first parameter is a select query that will get all the stored procedures in the database. Well, it also prepends "exec sp_helptext " to the procnames. You can execute the following query to see the result set.
select 'exec sp_helptext ' + name from sysobjects where type = 'p'
The extended proc will execute each row from the output of this query and display the result.
In this case, will display the definitions of all the SPs in the database. This proc is particularly useful in granting permissions in bulk.
Well we can very well copy the result from the above query and paste it again in the QA and execute it. But, then no harm being lazy at times :)
Posts
2 comments:
sp_msForEachDB becomes unreliable above 255 databases on a system, in case any reader is involved in such a project. I have close to 500 databases on one system, and scripts would regularly fail due to random and intermittent omissions.
I wrote my own version with a few enhancements. This only works in 2005 as written, but would only require a couple of changes to work in 2000 (namely, use sysdatabases instead of sys.databases, and change NVARCHAR(MAX) to NVARCHAR(4000)).
USE master;
GO
CREATE PROCEDURE dbo.AB_ForEachDB
@Command NVARCHAR(MAX),
@Pattern NVARCHAR(32) = '%'
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@sql NVARCHAR(MAX),
@db SYSNAME;
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT name
FROM sys.databases
WHERE name LIKE @pattern
ORDER BY name;
OPEN c;
FETCH NEXT FROM c INTO @db;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = REPLACE(@Command, '?', @db);
EXEC (@sql);
FETCH NEXT FROM c INTO @db;
END
CLOSE c;
DEALLOCATE c;
END
GO
EXEC dbo.AB_ForEachDB @Command = 'SELECT ''?'',COUNT(*) FROM ?.sys.tables;'
GO
-- DROP PROCEDURE dbo.AB_ForEachDB;
Aaron,
Thanks for pointing that out. Well, I guess thats where the experienced beats the fledgling :)
Post a Comment