Learning Ruby and Rails can be fun

by Rohit 7. May 2012 03:37

While starting Ruby and Rails, I read a couple of tutorials but soon lost interest until I met a monk. Yes, you read it right, a Monk, and this monk guided me to the path of enlightment. My journey to the world of Ruby on Rails started from here:

RubyMonk

The rubymonk Primer is a free introductory course on Ruby. It is an interactive learning platform from C42 Engineering. Learn Ruby with LESSONS, solve PROBLEMS or view the CONTENTS. RubyMonk combines the advantages of learning from a book, pairing with a mentor and actual hands-on coding and delivers it on a fun interactive platform for amateurs and enthusiasts who are discovering the Ruby language.

rubymonk.png

courtesy: www.rubymonk.com

 

In the journey, I met a man with code ... More...

Tags:

Ruby on Rails | Technology

Tool Pick #6: SQL Server Tools

by Rohit 28. April 2012 22:49

This week I have SQL Server tools for you.

SQL Scripter: Powerful tool for Microsoft SQL Server 7.0/2000/2005/2008 database administrators and developers to generate data scripts in a readable and executable T-SQL format.


NetWrix SQL Server Change Reporter: Monitor and review administrative changes on SQL servers and at database levels. Detect early all unauthorized and unwanted changes that can lead to server and database downtime. The Freeware Edition has few limitations.


SQL Sentry Plan Explorer: A FREE tool that builds upon the graphical plan view in SQL Server Management Studio (SSMS) to make query plan analysis more efficient. It is a lightweight standalone app that contains many of the plan analysis features introduced in SQL Sentry v6. It does not require a collector service or database.


FlySpeed SQL Query: Free SQL query tool for any database. It facilitates the process of building SQL by letting you quickly drag and drop tables to create queries, from simple to complex, and combine visual query building with direct SQL text editing.

Tags: , ,

Database | SQL Server | Tool Pick

Iterating through all the SQL Server databases and tables to execute any command.

by Rohit 25. April 2012 16:04
-

Sometimes you may want to execute a command against all the databases in an instance or all the tables in any database. SQL Server (2005 and above) contains two undocumented stored procedures, viz. sp_MSforeachdb and sp_MSforeacheable. To illustrate their use, I am taking a simple example to display all the databases and tables with the total space occupied.

What is the job of sp_MSforeachdb and sp_MSforeachtable?

Both the stored procedures are designed for iteration. With each iteration you can execute a command.

sp_MSforeachdb iterates through all the databases in the current instance, while sp_MSforeachtable iterates through all the tables in a database to perform a command.

You can view the details of each stored procedure by executing:

sp_help <name of system stored procedure>

from the query window. Below is the output of each:

 

Parameter Details
@command1 nvarchar(2000) field specifying the first command to run against each database. Can not be blank.
@replacechar nchar(1) field specifying the character in the command parameters to replace with the database name. Defaults: ?.
@command2 nvarchar(2000) field specifying the 2nd command to run against each database.
@command3 nvarchar(2000) field specifying the 3rd command to run against each database.
@precommand nvarchar(2000) field specifying a command to be run prior any commands against any database.
@postcommand nvarchar(2000) field specifying a command to be run after running all commands.

 

 

Script to display all databases with sizes in an instance using: sp_MSforeachdb

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

An alternate approach is by ignoring the system databases from the list. In that case:

EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
BACKUP DATABASE ? TO DISK = ''D:?.bak, WITH INIT'''

Script to display all the tables with total number of rows in a database using: sp_MSforeachtable

CREATE TABLE #t 
	( 
		Table_Name VARCHAR(80),
		Total_Rows CHAR(11),
		Reserved_Space VARCHAR(18), 
		Data_Size VARCHAR(18), 
		Index_Size VARCHAR(18),
		Unused_Space VARCHAR(18)
	) 

	INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

	SELECT * FROM #t ORDER BY Data_Size DESC

	DROP TABLE #t /* Drop Temp Table */

Note the ? used in all the code snippets above. It represents the name of the database or table returned at each iteration of the loop.

Disadvantages:

Both the stored procedures discussed above have a number of disadvantages. 

  1. Iteration uses Loop which is not recommended because Loops are harder to manage in databases and are slower than SET-based constructs.
  2. It is found that sp_MSforeachdb skips few databases from the list. Aaron Bertrand (Blog | twitter) writes in his blog about these issues. He also suggests a more reliable stored procedure to use instead of sp_MSforeachdb discussed above.

Tags: , ,

Database | Technology

About Rohit Prakash

Software Craftsman @ Fortune 100 Company. Technical Reviewer of few programming books.

A Single, Writer, Reader and Adventurist.

You can reach me at:

rohit [at] irohitable.com

Month List

Protected by Copyscape Web Plagiarism Software