Who is Active?  

Posted by ReelTym

SP_WHO2 on steroids....click title for script download.


ALTER PROC dbo.sp_WhoIsActive
(
--~
--Filters--Both inclusive and exclusive
--Set either filter to '' to disable
--Valid filter types are: session, program, database, login, and host
--Session is a session ID, and either 0 or '' can be used to indicate "all" sessions
--All other filter types support % or _ as wildcards
@filter sysname = '',
@filter_type VARCHAR(10) = 'session',
@not_filter sysname = '',
@not_filter_type VARCHAR(10) = 'session',

--Retrieve data about the calling session?
@show_own_spid BIT = 0,

--Retrieve data about system sessions?
@show_system_spids BIT = 0,

--Controls how sleeping SPIDs are handled, based on the idea of levels of interest
--0 does not pull any sleeping SPIDs
--1 pulls only those sleeping SPIDs that also have an open transaction
--2 pulls all sleeping SPIDs
@show_sleeping_spids TINYINT = 1,

--If 1, gets the full stored procedure or running batch, when available
--If 0, gets only the actual statement that is currently running in the batch or procedure
@get_full_inner_text BIT = 0,

--Get associated query plans for running tasks, if available
--If @get_plans = 1, gets the plan based on the request's statement offset
--If @get_plans = 2, gets the entire plan based on the request's plan_handle
@get_plans TINYINT = 0,

--Get the associated outer ad hoc query or stored procedure call, if available
@get_outer_command BIT = 0,

--Enables pulling transaction log write info and transaction duration
@get_transaction_info BIT = 0,

--Get information on active tasks, based on three interest levels
--Level 0 does not pull any task-related information
--Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers
--Level 2 pulls all available task-based metrics, including:
--number of active tasks, current wait stats, physical I/O, context switches, and blocker information
@get_task_info TINYINT = 1,

--Gets associated locks for each request, aggregated in an XML format
@get_locks BIT = 0,

--Get average time for past runs of an active query
--(based on the combination of plan handle, sql handle, and offset)
@get_avg_time BIT = 0,

--Walk the blocking chain and count the number of
--total SPIDs blocked all the way down by a given session
--Also enables task_info Level 1, if @get_task_info is set to 0
@find_block_leaders BIT = 0,

--Pull deltas on various metrics
--Interval in seconds to wait before doing the second data pull
@delta_interval TINYINT = 0,

--List of desired output columns, in desired order
--Note that the final output will be the intersection of all enabled features and all
--columns in the list. Therefore, only columns associated with enabled features will
--actually appear in the output. Likewise, removing columns from this list may effectively
--disable features, even if they are turned on
--
--Each element in this list must be one of the valid output column names. Names must be
--delimited by square brackets. White space, formatting, and additional characters are
--allowed, as long as the list contains exact matches of delimited valid column names.
@output_column_list VARCHAR(8000) = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',

--Column(s) by which to sort output, optionally with sort directions.
--Valid column choices:
--session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,
--tempdb_current, CPU, context_switches, used_memory, physical_io_delta,
--reads_delta, physical_reads_delta, writes_delta, tempdb_allocations_delta,
--tempdb_current_delta, CPU_delta, context_switches_delta, used_memory_delta,
--tasks, tran_start_time, open_tran_count, blocking_session_id, blocked_session_count,
--percent_complete, host_name, login_name, database_name, start_time
--
--Note that column names in the list must be bracket-delimited. Commas and/or white
--space are not required.
@sort_order VARCHAR(500) = '[start_time] ASC',

--Formats some of the output columns in a more "human readable" form
--0 disables output format
--1 formats the output for variable-width fonts
--2 formats the output for fixed-width fonts
@format_output TINYINT = 1,

--If set to a non-blank value, the script will attempt to insert into the specified
--destination table. Please note that the script will not verify that the table exists,
--or that it has the correct schema, before doing the insert.
--Table can be specified in one, two, or three-part format
@destination_table VARCHAR(4000) = '',

--If set to 1, no data collection will happen and no result set will be returned; instead,
--a CREATE TABLE statement will be returned via the @schema parameter, which will match
--the schema of the result set that would be returned by using the same collection of the
--rest of the parameters. The CREATE TABLE statement will have a placeholder token of
-- in place of an actual table name.
@return_schema BIT = 0,
@schema VARCHAR(MAX) = NULL OUTPUT,

--Help! What do I do?
@help BIT = 0
--~
)
/*
OUTPUT COLUMNS
--------------
Formatted/Non: [session_id] [smallint] NOT NULL
Session ID (a.k.a. SPID)

Formatted: [dd hh:mm:ss.mss] [varchar](15) NULL
Non-Formatted:
For an active request, time the query has been running
For a sleeping session, time the session has been connected

Formatted: [dd hh:mm:ss.mss (avg)] [varchar](15) NULL
Non-Formatted: [avg_elapsed_time] [int] NULL
(Requires @get_avg_time option)
How much time has the active portion of the query taken in the past, on average?

Formatted: [physical_io] [varchar](30) NULL
Non-Formatted: [physical_io] [bigint] NULL
Shows the number of physical I/Os, for active requests

Formatted: [reads] [varchar](30) NOT NULL
Non-Formatted: [reads] [bigint] NOT NULL
For an active request, number of reads done for the current query
For a sleeping session, total number of reads done over the lifetime of the session

Formatted: [physical_reads] [varchar](30) NOT NULL
Non-Formatted: [physical_reads] [bigint] NOT NULL
For an active request, number of physical reads done for the current query
For a sleeping session, total number of physical reads done over the lifetime of the session

Formatted: [writes] [varchar](30) NOT NULL
Non-Formatted: [writes] [bigint] NOT NULL
For an active request, number of writes done for the current query
For a sleeping session, total number of writes done over the lifetime of the session

Formatted: [tempdb_allocations] [varchar](30) NOT NULL
Non-Formatted: [tempdb_allocations] [bigint] NOT NULL
For an active request, number of TempDB writes done for the current query
For a sleeping session, total number of TempDB writes done over the lifetime of the session

Formatted: [tempdb_current] [varchar](30) NOT NULL
Non-Formatted: [tempdb_current] [bigint] NOT NULL
For an active request, number of TempDB pages currently allocated for the query
For a sleeping session, number of TempDB pages currently allocated for the session

Formatted: [CPU] [varchar](30) NOT NULL
Non-Formatted: [CPU] [int] NOT NULL
For an active request, total CPU time consumed by the current query
For a sleeping session, total CPU time consumed over the lifetime of the session

Formatted: [context_switches] [varchar](30) NULL
Non-Formatted: [context_switches] [bigint] NULL
Shows the number of context switches, for active requests

Formatted: [used_memory] [varchar](30) NOT NULL
Non-Formatted: [used_memory] [bigint] NOT NULL
For an active request, total memory consumption for the current query
For a sleeping session, total current memory consumption

Formatted: [physical_io_delta] [varchar](30) NULL
Non-Formatted: [physical_io_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of physical I/Os reported on the first and second collections.
If the request started after the first collection, the value will be NULL

Formatted: [reads_delta] [varchar](30) NULL
Non-Formatted: [reads_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of reads reported on the first and second collections.
If the request started after the first collection, the value will be NULL

Formatted: [physical_reads_delta] [varchar](30) NULL
Non-Formatted: [physical_reads_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of physical reads reported on the first and second collections.
If the request started after the first collection, the value will be NULL

Formatted: [writes_delta] [varchar](30) NULL
Non-Formatted: [writes_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of writes reported on the first and second collections.
If the request started after the first collection, the value will be NULL

Formatted: [tempdb_allocations_delta] [varchar](30) NULL
Non-Formatted: [tempdb_allocations_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of TempDB writes reported on the first and second collections.
If the request started after the first collection, the value will be NULL

Formatted: [tempdb_current_delta] [varchar](30) NULL
Non-Formatted: [tempdb_current_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of allocated TempDB pages reported on the first and second
collections. If the request started after the first collection, the value will be NULL

Formatted: [CPU_delta] [varchar](30) NULL
Non-Formatted: [CPU_delta] [int] NULL
(Requires @delta_interval option)
Difference between the CPU time reported on the first and second collections.
If the request started after the first collection, the value will be NULL

Formatted: [context_switches_delta] [varchar](30) NULL
Non-Formatted: [context_switches_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the context switches count reported on the first and second collections
If the request started after the first collection, the value will be NULL

Formatted: [used_memory_delta] [varchar](30) NULL
Non-Formatted: [used_memory_delta] [bigint] NULL
Difference between the memory usage reported on the first and second collections
If the request started after the first collection, the value will be NULL

Formatted: [tasks] [varchar](30) NULL
Non-Formatted: [tasks] [smallint] NULL
Number of worker tasks currently allocated, for active requests

Formatted/Non: [status] [varchar](30) NOT NULL
Activity status for the session (running, sleeping, etc)

Formatted/Non: [wait_info] [nvarchar](4000) NULL
Aggregates wait information, in the following format:
(Ax: Bms/Cms/Dms)E
A is the number of waiting tasks currently waiting on resource type E. B/C/D are wait
times, in milliseconds. If only one thread is waiting, its wait time will be shown as B.
If two tasks are waiting, each of their wait times will be shown (B/C). If three or more
tasks are waiting, the minimum, average, and maximum wait times will be shown (B/C/D).
If wait type E is a page latch wait and the page is of a "special" type (e.g. PFS, GAM, SGAM),
the page type will be identified.
If wait type E is CXPACKET, the nodeId from the query plan will be identified

Formatted/Non: [locks] [xml] NULL
(Requires @get_locks option)
Aggregates lock information, in XML format.
The lock XML includes the lock mode, locked object, and aggregates the number of requests.
Attempts are made to identify locked objects by name

Formatted/Non: [tran_start_time] [datetime] NULL
(Requires @get_transaction_info option)
Date and time that the first transaction opened by a session caused a transaction log
write to occur.

Formatted/Non: [tran_log_writes] [nvarchar](4000) NULL
(Requires @get_transaction_info option)
Aggregates transaction log write information, in the following format:
A:wB (C kB)
A is a database that has been touched by an active transaction
B is the number of log writes that have been made in the database as a result of the transaction
C is the number of log kilobytes consumed by the log records

Formatted: [open_tran_count] [varchar](30) NULL
Non-Formatted: [open_tran_count] [smallint] NULL
Shows the number of open transactions the session has open

Formatted: [sql_command] [xml] NULL
Non-Formatted: [sql_command] [nvarchar](max) NULL
(Requires @get_outer_command option)
Shows the "outer" SQL command, i.e. the text of the batch or RPC sent to the server,
if available

Formatted: [sql_text] [xml] NULL
Non-Formatted: [sql_text] [nvarchar](max) NULL
Shows the SQL text for active requests or the last statement executed
for sleeping sessions, if available in either case.
If @get_full_inner_text option is set, shows the full text of the batch.
Otherwise, shows only the active statement within the batch.
If the query text is locked, a special timeout message will be sent, in the following format:

If an error occurs, an error message will be sent, in the following format:


Formatted/Non: [query_plan] [xml] NULL
(Requires @get_plans option)
Shows the query plan for the request, if available.
If the plan is locked, a special timeout message will be sent, in the following format:

If an error occurs, an error message will be sent, in the following format:


Formatted/Non: [blocking_session_id] [smallint] NULL
When applicable, shows the blocking SPID

Formatted: [blocked_session_count] [varchar](30) NULL
Non-Formatted: [blocked_session_count] [smallint] NULL
(Requires @find_block_leaders option)
The total number of SPIDs blocked by this session,
all the way down the blocking chain.

Formatted: [percent_complete] [varchar](30) NULL
Non-Formatted: [percent_complete] [real] NULL
When applicable, shows the percent complete (e.g. for backups, restores, and some rollbacks)

Formatted/Non: [host_name] [sysname] NOT NULL
Shows the host name for the connection

Formatted/Non: [login_name] [sysname] NOT NULL
Shows the login name for the connection

Formatted/Non: [database_name] [sysname] NULL
Shows the connected database

Formatted/Non: [program_name] [sysname] NULL
Shows the reported program/application name

Formatted/Non: [start_time] [datetime] NOT NULL
For active requests, shows the time the request started
For sleeping sessions, shows the time the connection was made

Formatted/Non: [request_id] [int] NULL
For active requests, shows the request_id
Should be 0 unless MARS is being used

Formatted/Non: [collection_time] [datetime] NOT NULL
Time that this script's final SELECT ran
*/

Find Maximum Value in each Row – SQL Server  

Posted by ReelTym


DECLARE @t table (id int PRIMARY KEY, col1 int, col2 int, col3 int)

-- SAMPLE DATA
INSERT INTO @t SELECT 1, 45, 2, 14
INSERT INTO @t SELECT 2, 8, 1, 12
INSERT INTO @t SELECT 3, 21, 20, 8
INSERT INTO @t SELECT 4, 8, 5, 2
INSERT INTO @t SELECT 5, 23, 49, 7
INSERT INTO @t SELECT 6, 19, 7, 5
INSERT INTO @t SELECT 7, 7, 7, 2
INSERT INTO @t SELECT 8, 14, 17, 2

-- QUERY
SELECT id, MAX(col) AS maxValue
FROM
(
SELECT id, col FROM @t
UNPIVOT
(col FOR ListofColumns IN (col1,col2,col3))
AS unpivott) AS p
GROUP BY id

Calculate Age from Date Of Birth using SQL Server  

Posted by ReelTym


declare @birth datetime, @now datetime
select @birth ='19660527 12:34:22', @now = getdate()
select
years,
months,
days =
case when day( @birth ) > day( @now )
then
day( @now )
+ datediff(
day,
@birth,
dateadd(
mm,
datediff( mm, 0, @birth ) + 1,
0
)
) - 1
else day( @now ) - day( @birth )
end,
hours =
datepart(
hh,
convert( varchar(10), dateadd( mi, minutes, 0 ), 108 )
),
minutes =
datepart(
mi,
convert( varchar(10), dateadd( mi, minutes, 0 ), 108 )
)
from (
select
years,
months =
datediff(
mm,
dateadd( yy, years, @birth ),
@now
) +
case
when day( @now ) >= day( @birth )
then 0
else -1
end,
minutes =
datediff(
mi,
convert( varchar(8), @birth, 108 ),
convert( varchar(8), @now, 108 )
)
from (
select
years =
datediff( yy, @birth, @now )
+ case
when month(@now)>=month(@birth) then 0
else -1
end
) t
) as t

Venturing Web Resources  

Posted by ReelTym

Venturing PowerPoint Presentations

Word Documents (many of which are used in Venturing Leader training sessions at Philmont)

Venturing Worksheets

Venturing Word Documents for LDS Units

Venturing Literature and Resources. A variety of books, pamphlets, and videos are being developed to assist with organization, program, leadership, and activities. In particular, the Venturing Leader Manual will support leadership and planning. A comprehensive list of available material with current version numbers is available on BSA's official website. Click Here

Format Dates  

Posted by ReelTym

While designing one of the queries in SQL Server 2005, I came across a requirement to print dates without the day (i.e. only the month and year had to be printed) or print only the time and so on. I had seen a couple of questions on the forums about the same in the past, so thought of sharing these queries in case you needed them.

The original format in which the data was stored was 2010-06-30 14:15:15.390. Just declare it in your sql statement as follows:


DECLARE @CurrentDate DateTime
SET
@CurrentDate = '2010-06-30 14:15:15.390'

Here’s how we can achieve the desired output with the @CurrentDate:



Print Date in MM/YYYY format



SELECT DATENAME(m, @CurrentDate) 
+
' ' + CONVERT(varchar(4), DATEPART(year, @CurrentDate))
as 'MM/YYYY'


OUTPUT

image




Print Date in MM-DD-YY format



SELECT CONVERT(varchar(10), @CurrentDate, 110)
as 'MM-DD-YY'


OUTPUT

image


Print only the Time without the Date



SELECT CONVERT(varchar(8), CURRENT_TIMESTAMP, 112)


OUTPUT

image

Strip Characters using STUFF  

Posted by ReelTym


CREATE FUNCTION [dbo].[fn_StripCharacters]
( @String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression = '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = STUFF(@String, PatIndex(@MatchExpression,@String), 1, '')
RETURN @String
END

SELECT dbo.fn_StripCharacters('a1$%s#22d&39#f4$', '^a-z') as OnlyAlphaChars

View index metadata  

Posted by ReelTym

The sp_helpindex stored procedure gives useful information like name, description and key about the indexes on a table or view. However for retrieving detailed information, you should make use of the sys.indexes object catalog view.

Let us say you want to view the Index MetaData for the table Orders in the Northwind database.

Here’s the query for it:


SELECT
name,
type_desc,
is_unique,
allow_row_locks,
allow_page_locks,
is_disabled,
fill_factor
FROM sys.indexes
WHERE object_id = OBJECT_ID('Orders')


OUTPUT

image

Common uses for sp_MSforeachdb  

Posted by ReelTym

sp_MSforeachdb iterates through each database in a SQL Server instance. Instead of using a cursor, this Undocumented Stored Procedure is of immense help when I need to run a command against all the databases in my local server. Here are some scenarios where the sp_MSforeachdb can be practically used for your day to day tasks:


  1. Print all the database names in a SQL Server Instance


  2. EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'

  3. Print all the tables in all the databases of a SQL Server Instance


  4. EXEC sp_MSforeachdb 'USE ? SELECT DB_NAME() + ''.'' + OBJECT_NAME(object_Id) FROM sys.tables'

  5. The example shown above prints the tables for master, model, msdb and tempdb. If you want to eliminate these databases in the query use, do the following:


  6. EXEC sp_MSforeachdb '
    USE ?
    SELECT OBJECT_NAME(object_Id)
    FROM sys.tables
    where DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
    '

  7. Display the size of all databases in a SQL Server instance


  8. EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'

  9. Determine all the physical names and attributes(size,growth,usage) of all databases in a SQL Server instance


  10. EXEC sp_MSforeachdb 'USE ? EXEC sp_helpfile;'

  11. Change Owner of all databases to 'sa'


  12. EXEC sp_MSforeachdb 'USE ?; EXEC sp_changedbowner ''sa'''

  13. Check the Logical and Physical integrity of all objects in the database


  14. sp_MSforeachdb 'DBCC CHECKDB(?)'

    Similarly you can backup all databases at one go or do a CHECKSUM using this useful procedure. There are many more ways to use it. Got a useful tip on sp_MSforeachdb? Share it using the comments below.

Common uses for sp_MSforeachtable  

Posted by ReelTym

sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure:


  1. Display the size of all tables in a database


  2. USE NORTHWIND
    EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

  3. Display Number of Rows in all Tables in a database


  4. USE YOURDBNAME
    EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'

  5. Rebuild all indexes of all tables in a database


  6. USE YOURDBNAME
    EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"


    Note: DBCC DBREINDEX has been deprecated in SQL 2005. Microsoft says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead."
  7. Disable all constraints of all tables in a database


  8. USE YOURDBNAME
    EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

  9. Disable all Triggers of all tables in a database


  10. USE YOURDBNAME
    EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

  11. Delete all data from all tables in your database


  12. -- disable referential integrity
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    EXEC sp_MSForEachTable '
    IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
    DELETE FROM ?
    ELSE
    TRUNCATE TABLE ?
    '
    GO
    -- enable referential integrity again
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    GO

  13. To RESEED all table to 0, use this script


  14. EXEC sp_MSForEachTable '
    IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
    DBCC CHECKIDENT (''?'', RESEED, 0)
    '
    GO

    The two tips shown above have been taken from http://blogs.officezealot.com/mauro/archive/2006/03/12/9402.aspx and http://www.sqljunkies.com/WebLog/roman/archive/2006/03/08/18620.aspx

  15. Reclaim space from dropped variable-length columns in tables or indexed views


  16. USE YOURDBNAME
    EXEC sp_MSforeachtable 'DBCC CLEANTABLE(0,''?'') WITH NO_INFOMSGS; ';

  17. Update Statistics of all Tables in a database


  18. USE YOURDBNAME
    EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'