sp_GetAllTableSizes

use master go CREATE PROCEDURE sp_GetAllTableSizes AS DECLARE @tabname varchar(128) --Cursor to get the name of all user tables from the sysobjects listing DECLARE table_cur CURSOR FAST_FORWARD FOR SELECT [name] FROM dbo.sysobjects (NOLOCK) WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 --A procedure level temp table to store the results CREATE TABLE #TempTable ( tableName varchar(128), numberofRows int, reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50) ) --Open the cursor OPEN table_cur --Get the first table name from the cursor FETCH NEXT FROM table_cur INTO @tabname --Loop until the cursor was not able to fetch WHILE (@@Fetch_Status >= 0) BEGIN --Dump the results of the sp_spaceused query to the temp table INSERT #TempTable EXEC sp_spaceused @tabname --Get the next table name FETCH NEXT FROM table_cur INTO @tabname END --Get rid of the cursor CLOSE table_cur DEALLOCATE table_cur --Select all records so we can use the reults SELECT * FROM #TempTable ORDER BY numberofRows DESC --Final cleanup! DROP TABLE #TempTable GO

sp_foreachdb

This is a replacement for the undocumented sp_MSforeachdb system stored proc. USE [master]; GO CREATE PROCEDURE dbo.sp_foreachdb @command NVARCHAR(MAX), @replace_character NCHAR(1) = N'?', @print_dbname BIT = 0, @print_command_only BIT = 0, @suppress_quotename BIT = 0, @system_only BIT = NULL, @user_only BIT = NULL, @name_pattern NVARCHAR(300) = N'%', @database_list NVARCHAR(MAX) = NULL, @recovery_model_desc NVARCHAR(120) = NULL, @compatibility_level TINYINT = NULL, @state_desc NVARCHAR(120) = N'ONLINE', @is_read_only BIT = 0, @is_auto_close_on BIT = NULL, @is_auto_shrink_on BIT = NULL, @is_broker_enabled BIT = NULL AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX), @dblist NVARCHAR(MAX), @db NVARCHAR(300), @i INT; IF @database_list > N'' BEGIN ;WITH n(n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY s1.name) - 1 FROM sys.objects AS s1 CROSS JOIN sys.objects AS s2 ) SELECT @dblist = REPLACE(REPLACE(REPLACE(x,'',','), '',''),'','') FROM ( SELECT DISTINCT x = 'N''' + LTRIM(RTRIM(SUBSTRING( @database_list, n, CHARINDEX(',', @database_list + ',', n) - n))) + '''' FROM n WHERE n <= LEN(@database_list) AND SUBSTRING(',' + @database_list, n, 1) = ',' FOR XML PATH('') ) AS y(x); END CREATE TABLE #x(db NVARCHAR(300)); SET @sql = N'SELECT name FROM sys.databases WHERE 1=1' + CASE WHEN @system_only = 1 THEN ' AND database_id IN (1,2,3,4)' ELSE '' END + CASE WHEN @user_only = 1 THEN ' AND database_id NOT IN (1,2,3,4)' ELSE '' END + CASE WHEN @name_pattern <> N'%' THEN ' AND name LIKE N''%' + REPLACE(@name_pattern, '''', '''''') + '%''' ELSE '' END + CASE WHEN @dblist IS NOT NULL THEN ' AND name IN (' + @dblist + ')' ELSE '' END + CASE WHEN @recovery_model_desc IS NOT NULL THEN ' AND recovery_model_desc = N''' + @recovery_model_desc + '''' ELSE '' END + CASE WHEN @compatibility_level IS NOT NULL THEN ' AND compatibility_level = ' + RTRIM(@compatibility_level) ELSE '' END + CASE WHEN @state_desc IS NOT NULL THEN ' AND state_desc = N''' + @state_desc + '''' ELSE '' END + CASE WHEN @is_read_only IS NOT NULL THEN ' AND is_read_only = ' + RTRIM(@is_read_only) ELSE '' END + CASE WHEN @is_auto_close_on IS NOT NULL THEN ' AND is_auto_close_on = ' + RTRIM(@is_auto_close_on) ELSE '' END + CASE WHEN @is_auto_shrink_on IS NOT NULL THEN ' AND is_auto_shrink_on = ' + RTRIM(@is_auto_shrink_on) ELSE '' END + CASE WHEN @is_broker_enabled IS NOT NULL THEN ' AND is_broker_enabled = ' + RTRIM(@is_broker_enabled) ELSE '' END; INSERT #x EXEC sp_executesql @sql; DECLARE c CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT CASE WHEN @suppress_quotename = 1 THEN db ELSE QUOTENAME(db) END FROM #x ORDER BY db; OPEN c; FETCH NEXT FROM c INTO @db; WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = REPLACE(@command, @replace_character, @db); IF @print_command_only = 1 BEGIN PRINT '/* For ' + @db + ': */' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + @sql + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10); END ELSE BEGIN IF @print_dbname = 1 BEGIN PRINT '/* ' + @db + ' */'; END EXEC sp_executesql @sql; END FETCH NEXT FROM c INTO @db; END CLOSE c; DEALLOCATE c; END GO

sp_findit

use master
go

CREATE PROCEDURE [sp_findit] ( @value sysname ) 
AS
BEGIN

   SET NOCOUNT ON;

          SELECT distinct so.type
               , so.name
            FROM syscomments sc
      INNER JOIN sysobjects so
              ON sc.id=so.id
           WHERE sc.TEXT LIKE '%' + @value + '%'
           UNION
          SELECT distinct so.type
               , so.name
            FROM syscolumns sc
      INNER JOIN sysobjects so
              ON sc.id = so.id
           WHERE sc.name LIKE '%' + @value + '%'
        ORDER BY 1
               , 2

END --sp_findit()

Oracle: Lock and Kill User Sessions

ALTER USER DBUSER ACCOUNT LOCK; SELECT 'alter system kill session ''' || sid || ',' || serial# || '''; ' || sql_id death FROM v$session WHERE username = 'DBUSER';

Get All Table Sizes

use master go CREATE PROCEDURE sp_GetAllTableSizes AS BEGIN DECLARE @tabname varchar(128) DECLARE table_cur CURSOR FAST_FORWARD FOR SELECT [name] FROM dbo.sysobjects (NOLOCK) WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 CREATE TABLE #TempTable ( tableName varchar(128), numberofRows int, reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50) ) OPEN table_cur FETCH NEXT FROM table_cur INTO @tabname WHILE (@@Fetch_Status >= 0) BEGIN INSERT #TempTable EXEC sp_spaceused @tabname FETCH NEXT FROM table_cur INTO @tabname END CLOSE table_cur DEALLOCATE table_cur SELECT * FROM #TempTable ORDER BY numberofRows DESC DROP TABLE #TempTable END --sp_GetAllTableSizes GO

Memory Status

dbcc memorystatus select * from sys.dm_exec_requests dbcc sqlperf (spinlockstats) select * from sys.dm_os_wait_stats select * from sys.dm_os_waiting_tasks select * from sys.dm_os_latch_stats select * from sys.dm_os_spinlock_stats

sp_MSforeachtable

You can use the sp_MSforeachtable undocumented system stored procedure to accomplish tasks on all tables of a database such as rebuilding indexes or recomputing statistics. This script rebuilds all indexes with a fillfactor of 80 in every table in the pubs database: USE pubs GO EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO