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

Filed under , 0 comments

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

Filed under , 0 comments

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

Filed under , 0 comments

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

Filed under , 0 comments

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()'

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


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

  3. 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:


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

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


    5. EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'

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


    6. EXEC sp_MSforeachdb 'USE ? EXEC sp_helpfile;'

  6. Change Owner of all databases to 'sa'


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

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


    8. 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.

Filed under , 0 comments

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 '?'"

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


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

  3. Rebuild all indexes of all tables in a database


    4. 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."
  4. Disable all constraints of all tables in a database


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

  5. Disable all Triggers of all tables in a database


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

  6. Delete all data from all tables in your database


    7. -- 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

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


    8. 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

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


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

  9. Update Statistics of all Tables in a database


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

Filed under , 0 comments

SQL Examples  

Posted by ReelTym

Welcome to SQL Server Examples

The examples on this portal will provide you with the building blocks necessary to quickly resolve different TSQL coding issues. You can browse the examples by using the Examples link on the main menu. You can also download all the examples on this site by clicking HERE.

Also available on this site is a SQL Server monitoring tool known as the SQL Server DBA Dashboard tool. This tool provides both high level and detailed reports to help monitor your SQL Server instances. With this tool you can quickly identify the resources used by specific TSQL statements and stored procedures.

Filed under , 0 comments
Newer Posts Older Posts